Move postgreSQL server data to new Folder Location

 Move the entire postgres server files from default location to a custom location on the server

A word of Caution:

1. This procedure involves a database shutdown and restart needed. 
2. Please make sure you have  change request raised if you are working on production database and get it approved before scheduling this change. 
3. You must notify the application team about the change as well and set the down time expectations.
4. It is better to maintain the standards in the naming convention for the common scripts to monitor and support any activities.

Pre-Steps:

• Create the required folder structure and modify the ownership and permissions on the folder structure similar to the below steps

        $ sudo su - root

        $ mkdir -p /myorg/postgres/

        $ chown -R postgres:postgres /myorg/postgres

        $ chown -R 755 /myorg/postgres

Note: 

1. Do not copy any files while the server is running. This will lead to inconsistent state of the database and may fail to start.
2. Ensure sufficient space is allocation to copy the files and for the future growth as well.
3. You can further re-org the folders within the new folder but that needs lot of planning in advance to group the files in the original location and copy.

Migration steps:

• Backup the existing postgres configuration file (use your proper location below)

            $ sudo su - root

            $ cp -p /etc/postgresql/13/main/postgresql.conf /etc/postgresql/13/main/postgresql.conf.<backupname>

•  Update the configuration file with the new location

             $ vi /etc/postgresql/13/main/postgresql.conf

                # data_directory = '/var/lib/postgres/13/main'    # commenting old location 

                data_directory = '/myorg/postgres'

    << Your DB outage window starts now  >>

• Stop the postgres db

          $ sudo systemctl stop postgres

          $ sudo systemctl status postgres

            Ensure no errors during the shutdown 

            $ tail -100f /var/log/postgresql/postgresql-13-main.log

• Copy the files into new folder location

            $ cp -p -r /var/lib/postgres/13/main /myorg/postgres

            $ ls -ltr /myorg/postgres

            Ensure all files are having correct permissions 

• Rename the original location and keep it for few days for rollback purposes

            $ mv /var/lib/postgres/13/main /var/lib/postgres/13/main.<change#>

            This may be needed just to ensure that all the files have been migrated to new location completely. If not, this step will lead to an error when starting the database.

• Start the postgres db now

            $ sudo systemctl start postgres

            Ensure no errros logged during the startup process

            $ tail -100f /var/log/postgresql/postgresql-13-main.log

• If no errors, your database server starts as normal.
• Check the data_directory

            $ sudo su - postgres

            $ psql

            postgres# show data_directory;

            Here you should see the new location 

• After few days, you can now drop the old location to free up the space.

            $ rm -rf  /var/lib/postgres/13/main.<change#>