w3resource

Copying a PostgreSQL Database to another Server


Copying a PostgreSQL Database to another Server:

Copying a PostgreSQL database to another server is a common task, especially when setting up backups, testing environments, or migrating data. PostgreSQL offers several tools to make this process efficient, primarily using pg_dump and pg_restore or by directly piping data over a network.

Method 1: Using pg_dump and pg_restore

The pg_dump utility creates a backup of the database in a format that pg_restore can then use to recreate the database on another server.

1. Dump the Database on the Source Server

# Run pg_dump to create a database backup file
pg_dump -U username -h source_host -p source_port -F c -b -v -f backup_file.dump database_name

Explanation:

  • -U username: Specifies the username.
  • -h source_host: Specifies the source server hostname or IP.
  • -p source_port: Specifies the source server port.
  • -F c: Chooses the custom format, suitable for pg_restore.
  • -b: Includes large objects in the backup.
  • -v: Enables verbose output.
  • -f backup_file.dump: Names the output backup file.

Copy the Backup file to the target Server:

# Transfer backup file to the target server using scp
scp backup_file.dump target_user@target_host:/path/to/directory

Explanation:

  • scp: Securely copies the file to the target server.
  • target_user@target_host: Specifies the target server’s username and hostname or IP.
  • /path/to/directory: Target directory path to store the backup file.

Restore the Database on the target Server:

# Run pg_restore to restore the database on the target server
pg_restore -U username -h target_host -p target_port -d new_database_name -v /path/to/backup_file.dump

Explanation:

  • -U username: Specifies the username.
  • -h target_host: Specifies the target server hostname or IP.
  • -p target_port: Specifies the target server port.
  • -d new_database_name: The target database name to restore into.
  • /path/to/backup_file.dump: The path to the transferred backup file.

Method 2: Directly Copying the Database Over a Network

If both servers are accessible, you can pipe the output from pg_dump directly to psql on the target server.

# Directly copy database from source to target server over a network
pg_dump -U username -h source_host -p source_port database_name | psql -U username -h target_host -p target_port -d new_database_name

Explanation:

  • The pipe (|) allows the pg_dump output to be used directly by psql on the target server.
  • This method requires network connectivity between the servers and can be faster as it avoids writing to an intermediate file.

Example Code and Explanation

1. Dump the Database

# Dump the database into a custom format file
pg_dump -U admin_user -h 192.168.1.10 -p 5432 -F c -b -v -f mydb_backup.dump my_database

2. Transfer the Backup File

# Transfer the dump file to the target server
scp mydb_backup.dump [email protected]:/backups/

3. Restore the Database

# Restore the database on the target server
pg_restore -U admin_user -h 192.168.1.20 -p 5432 -d new_database -v /backups/mydb_backup.dump

Important Notes:

  • Permissions: Ensure both source and target servers allow access for the specified user.
  • Network Access: For direct copying over the network, open the necessary ports (e.g., 5432).
  • Backup Format: The custom format -F c enables faster and more flexible restores with pg_restore.

All PostgreSQL Questions, Answers, and Code Snippets Collection.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/PostgreSQL/snippets/copy-postgresql-database-to-another-server.php