I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in development mode so I can delete the old data if it makes transfer easier at this point of time. What is the best way to do this?

Is PostgreSQL tablespace something which I should look at?

  • 1
    Tablespaces would be the way to go if you wanted to move part of your database(s) to a separate drive. Nov 19, 2012 at 23:42
  • This answer suggests that tablespaces should NOT go on an external hard drive: dba.stackexchange.com/a/62037/41823 Not sure I understand the risk if you're already putting the data on an external?
    – Statwonk
    Jun 29, 2014 at 0:14

4 Answers 4


The following should do it:

  1. Shut down PostgreSQL
  2. Make sure PostgreSQL does not run any longer
  3. Check that PostgreSQL is really stopped
  4. Copy the old data directory to the new drive
    The data directory is usually defined through a commandline parameter (-D) for your service or through the PGDATA environment variable.
  5. Update your PostgreSQL configuration (service, environment var) to point to the new data directory. In Windows you can use the sc utility or pg_ctl (from the Postgres binaries)
  6. If you copied the data, rename the old data directory to make sure PostgreSQL cannot find it any longer. That way you'll get an error message if you didn't get the configuration right.
  7. Start the service
  8. If everything is running fine, you can delete the old directory.
  • In 4th point, what do you mean by 'This is usually defined through a commandline parameter...'?
    – codecool
    Nov 19, 2012 at 15:52
  • @codecool: there are several different ways how this could be defined. Usually it's part of the service definition. But it depends on your operating system, distribution and personal changes.
    – user1822
    Nov 19, 2012 at 15:54
  • This worked but I used the regedit instead of the command line for Windows. Whew. My disk was just about to run out of space but now I have it safely operating elsewhere with enough space.
    – trench
    Jul 13, 2016 at 18:02
  • 1
    Here is nice step-by-step blog post with specific details, up-to-date for typical ubuntu and 9.5 DB version: digitalocean.com/community/tutorials/…
    – JaakL
    Jan 30, 2017 at 21:04
  1. Stop postgresql service
  2. cp -a source_data_directory destination_data_directory
  3. chown -R postgres_user /destination_data_directory
  4. export PGDATA=destination_data_directory
  5. Changing data directory to destination_data_directory within postgresql.conf
  6. pg_ctl start
  • I glad you remembered the chown command for the new folder. May 6, 2016 at 21:49

Here is a description of how to move an existing PostgreSQL database to a new location (folder, partition), on Arch Linux (this procedure should be similar for other Linux distributions).

pg_dumpall is a utility for writing out (dumping) all PostgreSQL databases of a cluster into one script file. ... It does this by calling pg_dump for each database in the cluster. ...

For example:

## To dump all databases:
$ pg_dumpall > /tmp/db.out               ## backup all postgres databases to file
$ pg_dumpall -Fc dbname > /tmp/outfile   ## backup a database

## To reload database(s) from that file:
$ psql -f /tmp/db.out postgres


You need to:

  • export (pg_dumpall) database
  • create new EMPTY directory
  • edit postgresql.conf file
  • restart postgres server service
  • load old data from dump file into new database

Summary of commands

I'm leaving my shell prompts (hostname ...) to clarify what shell I am in as I proceed.

[victoria@victoria ~]$ postgres --version     ## postgres (PostgreSQL) 11.2
[victoria@victoria claws]$ sudo -u postgres -i
[postgres@victoria ~]$ pg_dumpall > /tmp/db.out
[postgres@victoria ~]$ psql
[postgres]# SHOW data_directory;              ## /var/lib/postgres/data
[postgres]# exit
[victoria@victoria postgres]$ mkdir /mnt/Vancouver/programming/rdb/postgres/postgresdb
## IMPORTANT: dir must be empty:
[victoria@victoria postgres]$ l postgresdb/   ## total 0
[victoria@victoria postgres]$ sudo chown postgres:root /mnt/Vancouver/programming/rdb/postgres/postgresdb/
[victoria@victoria postgres]$ sudo systemctl status postgresql
  May 02 19:55:21 victoria systemd[1]: Started PostgreSQL database server.
[postgres@victoria ~]$ initdb --locale en_US.UTF-8 -E UTF8 -D '/mnt/Vancouver/programming/rdb/postgres/postgresdb/data'
[postgres@victoria ~]$ exit
[victoria@victoria postgres]$ sudo vim /var/lib/postgres/data/postgresql.conf
  ## added line:
  data_directory = '/mnt/Vancouver/programming/rdb/postgres/postgresdb/data'
[victoria@victoria postgres]$ sudo -u postgres -i
[postgres@victoria ~]$ psql
[postgres]# SHOW data_directory;
  ## /var/lib/postgres/data   ## << Old dir; need to restart postgresql service
[postgres]# \q
[victoria@victoria postgres]$ sudo systemctl restart postgresql
[victoria@victoria postgres]$ sudo -u postgres -i
[postgres@victoria ~]$ psql
[postgres]# SHOW data_directory;
  ## /mnt/Vancouver/programming/rdb/postgres/postgresdb/data
[postgres]# \q
[postgres@victoria ~]$ psql -f /tmp/db.out postgres
  [ ... SNIP! ... ]
[postgres@victoria ~]$ exit
## Done!  :-D


See my accompanying blog post, How to Move an Existing PostgreSQL Database on Arch Linux, for commented code and output.---



@a_horse_with_no_name answers works great but I needed to modified it bit so systemctl works with it.

sudo systemctl edit postgresql-13

# Note: This overrides base systemctl file

Note: I had to explicitly clear Environment before setting it again, as it is an additive setting, similar to other lists like ExecStart (as a whole, not per-variable) and EnvironmentFile;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.