3

I am looking to move the location of a pgsql 13 database from it's default to another disk.

I initially followed this guide link

But this is for v9.5, not 13. My challenge is that the location of the database - found from running the below command - is also where the configuration files are stored.

SHOW data_directory;
     data_directory
------------------------
 /var/lib/pgsql/13/data
(1 row)

SHOW config_file;
     config_file
----------------------------------------
 /var/lib/pgsql/13/data/postgresql.conf
(1 row)

With version 9.5 the configuration files were in a separate area, so at this point I got stuck with the guide.

It seems if I want to move the database location I also have to move all the configuration files as well.

I have tried moving the entire data folder to the new location and restarting postgres but no luck.

Any help would be appreciated.

fraserc182
  • 237
  • 1
  • 4
  • 13
  • 2
    "*With version 9.5 the configuration files were in a separate area*" - no, the were not. Some Linux distribution however did separate the config files from the data directory. But e.g. on CentOS the config file is always located in the data directory. –  May 13 '21 at 11:16
  • 2
    "*I have tried moving the entire data folder to the new location and restarting postgres but no luck.*" that is the right approach, but you will need to change the service definition to use the new data directory. How you do that depends on your Linux distribution –  May 13 '21 at 11:34

1 Answers1

6

Assuming your configuration files are located under $PG_DATA, where they belong:

  1. Shut down the (old) database
  2. Copy the data directory to the new location (use cp -rp, or rsync -acv, or tar, or cpio, ...) Make sure that file attributes and ownership are preserved by the copy. The pgdata directory should be mode == 0600, and owner.group == postgres.postgres.
  3. [optionally] rename the old data directory
  4. [optionally] you may want to edit the configuration files at the new location
  5. edit the startup file (in /etc/init.d/postgresql ) and make sure $PG_DATA points to the new location. [note: this is for ubuntu; other distributions may us a different starting mechanism]
  6. Start the new database, and check if it runs (ps auxw| grep postgres, and if you can connect (psql -U postgres postgres)
  7. [optionally] remove the directory tree at the old location.
senden9
  • 336
  • 1
  • 5
  • 16
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • Hi thanks for the response, unfortunately there is no startup file in init.d relating to postgresql. – fraserc182 May 13 '21 at 13:06
  • I found the file, it exists under /usr/lib/systemd/system/postgresql-13.service. Once I found it I followed your steps and this has sorted it, didn't have to edit the config file as it is looking at ConfigDir, which is set to the environment variable $PG_DATA. Thanks for the help. – fraserc182 May 13 '21 at 13:12