CODEWITHSUNDEEP
postgresqlpostgresql-13
fraserc182
fraserc182

Reputation: 313

How to move location of postrgresql 13 database

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.

Upvotes: 3

Views: 9510

Answers (1)

wildplasser
wildplasser

Reputation: 44240

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.

Upvotes: 6

Related Questions