Copying/ Migrating PostgreSQL to another server (Live)

  1. Using Virtual backups style, i.e., pg_dump or pg_dumpall

The command can be used as below:

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

 

  1. Using Physical backups i.e, pg_basebackup.

Step1: Make sure you have the PosgreSQL is installed on remote server.

Step2: Copy the backup from the original server taken with pg_basebackup.

Step3 Remove all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

Step4: Restore the database files from your file system backup. Be sure that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

Step5: Remove any files present in pg_xlog/; these came from the file system backup and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it with proper permissions, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.

Step6: If you have unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so you still have the unmodified files if a problem occurs and you have to start over.)

Step7: Create a recovery command file recovery.conf in the cluster data. You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful.

Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the

like0