Copying PostgreSQL database to another server

Question

I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?

Answer 1

This is the easiest solution:

pg_dump --dbname=postgresql://[user]:[password]@[host]:[port]/[database] | psql --dbname=postgresql://[user]:[password]@[host]:[port]/[database]

The first part, pg_dump, is downloading the database you want to copy.

The second part, psql, is uploading to that address.

An example:

pg_dump --dbname=postgresql://postgres:password@originaldatabase.cluster-awsstring.eu-west-1.rds.amazonaws.com:5432/originaldatabasename | psql --dbname=postgresql://postgres:password@newdatabase.cluster-awsstring.eu-west-1.rds.amazonaws.com:5432/newdatabasename

I found the previous examples didn't spell out how to construct the --dbname string or in the docs. Ref: What is the format for the PostgreSQL connection string / URL?

Answer 2

Let me share a Linux shell script to copy your table data from one server to another PostgreSQL server.

Reference taken from this blog:

Linux Bash Shell Script for data migration between PostgreSQL Servers:

#!/bin/bash
psql \
    -X \
    -U user_name \
    -h host_name1 \
    -d database_name \
    -c "\\copy tbl_Students to stdout" \
| \
psql \
    -X \
    -U user_name \
    -h host_name2 \
    -d database_name \
    -c "\\copy tbl_Students from stdin"

I am just migrating the data; please create a blank table at your destination/second database server.

This is a utility script. Further, you can modify the script for generic use something like by adding parameters for host_name, database_name, table_name and others

Answer 3

If you are more comfortable with a GUI, you can use the pgAdmin software.

• Connect to your source and destination servers
• Right-click on the source db > backup
• Right-click on the destination server > create > database. Use the same properties as the source db (you can see the properties of the source db by right-click > properties)
• Right-click on the created db > restore.

Answer 4

Here is an example using pg_basebackup

I chose to go this route because it backs up the entire database cluster (users, databases, etc.).

I'm posting this as a solution on here because it details every step I had to take, feel free to add recommendations or improvements after reading other answers on here and doing some more research.

For Postgres 12 and Ubuntu 18.04 I had to do these actions:

---

On the server that is currently running the database:

Update pg_hba.conf, for me located at /etc/postgresql/12/main/pg_hba.conf

Add the following line (substitute 192.168.0.100 with the IP address of the server you want to copy the database to).

host  replication  postgres  192.168.0.100/32  trust

Update postgresql.conf, for me located at /etc/postgresql/12/main/postgresql.conf. Add the following line:

listen_addresses = '*'

Restart postgres:

sudo service postgresql restart

---

On the host you want to copy the database cluster to:

sudo service postgresql stop

sudo su root

rm -rf /var/lib/postgresql/12/main/*

exit

sudo -u postgres pg_basebackup -h 192.168.0.101 -U postgres -D /var/lib/postgresql/12/main/

sudo service postgresql start

Big picture - stop the service, delete everything in the data directory (mine is in /var/lib/postgreql/12). The permissions on this directory are drwx------ with user and group postgres. I could only do this as root, not even with sudo -u postgres. I'm unsure why. Ensure you are doing this on the new server you want to copy the database to! You are deleting the entire database cluster.

Make sure to change the IP address from 192.168.0.101 to the IP address you are copying the database from. Copy the data from the original server with pg_basebackup. Start the service.

Update pg_hba.conf and postgresql.conf to match the original server configuration - before you made any changes adding the replication line and the listen_addresses line (in my care I had to add the ability to log-in locally via md5 to pg_hba.conf).

Note there are considerations for max_wal_senders and wal_level that can be found in the documentation. I did not have to do anything with this.

Answer 5

pg_basebackup seems to be the better way of doing this now, especially for large databases.

You can copy a database from a server with the same or older major version. Or more precisely:

pg_basebackup works with servers of the same or an older major version, down to 9.1. However, WAL streaming mode (-X stream) only works with server version 9.3 and later, and tar format mode (--format=tar) of the current version only works with server version 9.5 or later.

For that you need on the source server:

1. listen_addresses = '*' to be able to connect from the target server. Make sure port 5432 is open for that matter.
2. At least 1 available replication connection: max_wal_senders = 1 (-X fetch), 2 for -X stream (the default in case of PostgreSQL 12), or more.
3. wal_level = replica or higher to be able to set max_wal_senders > 0.
4. host replication postgres DST_IP/32 trust in pg_hba.conf. This grants access to the pg cluster to anyone from the DST_IP machine. You might want to resort to a more secure option.

Changes 1, 2, 3 require server restart, change 4 requires reload.

On the target server:

# systemctl stop postgresql@VERSION-NAME
postgres$ pg_basebackup -h SRC_IP -U postgres -D VERSION/NAME --progress
# systemctl start postgresql@VERSION-NAME

Answer 6

You don't need to create an intermediate file. You can do

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

or

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

using psql or pg_dump to connect to a remote host.

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use a compressed tunnel

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

or

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

but this solution also requires to get a session in both ends.

Note: pg_dump is for backing up and psql is for restoring. So, the first command in this answer is to copy from local to remote and the second one is from remote to local. More -> https://www.postgresql.org/docs/9.6/app-pgdump.html

Answer 7

Accepted answer is correct, but if you want to avoid entering the password interactively, you can use this:

PGPASSWORD={{export_db_password}} pg_dump --create -h {{export_db_host}} -U {{export_db_user}} {{export_db_name}} | PGPASSWORD={{import_db_password}} psql -h {{import_db_host}} -U {{import_db_user}} {{import_db_name}}

Answer 8

---

Dump your database : pg_dump database_name_name > backup.sql

---

Import your database back: psql db_name < backup.sql

Answer 9

I struggled quite a lot and eventually the method that allowed me to make it work with Rails 4 was:

on your old server

sudo su - postgres
pg_dump -c --inserts old_db_name > dump.sql

I had to use the postgres linux user to create the dump. also i had to use -c to force the creation of the database on the new server. --inserts tells it to use the INSERT() syntax which otherwise would not work for me :(

then, on the new server, simpy:

sudo su - postgres
psql new_database_name < dump.sql

to transfer the dump.sql file between server I simply used the "cat" to print the content and than "nano" to recreate it copypasting the content.

Also, the ROLE i was using on the two database was different so i had to find-replace all the owner name in the dump.

Answer 10

Run this command with database name, you want to backup, to take dump of DB.

 pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 eg. pg_dump -U postgres mydbname -f mydbnamedump.sql

Now scp this dump file to remote machine where you want to copy DB.

eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/

On remote machine run following command in ~/some/folder to restore the DB.

 psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

 eg. psql -U postgres -d mynewdb -f mydbnamedump.sql

Answer 11

If you are looking to migrate between versions (eg you updated postgres and have 9.1 running on localhost:5432 and 9.3 running on localhost:5434) you can run:

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

Check out the migration docs.

Answer 12

Use pg_dump, and later psql or pg_restore - depending whether you choose -Fp or -Fc options to pg_dump.

Example of usage:

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

Answer 13

pg_dump the_db_name > the_backup.sql

Then copy the backup to your development server, restore with:

psql the_new_dev_db < the_backup.sql