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.

2. 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.

3. 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.