How to migrate database from a server to another server.

Step 1 until step 3 are done on source server

1. Login as the postgres user
type the following command in linux prompt(in my case, ubuntu)

[email protected] - postgres

when i did this,i got authentication failures.
this is because by default, no any password assigned to the postgres superuser
we should explicitly SET the postgres user password using something like below command :

[email protected]:~$ sudo passwd postgres
Enter new UNIX password:****
Retype new UNIX password:****
passwd: password updated successfully

2. On postgres shell, execute pg_dump in order to copy the database on source server to a file with extension “*.bak” (actually it can be
other extensions as well, please google if you want to know other options)

[email protected]:~$pg_dump name_of_database > name_of_src_db_file.bak

the resulting backup file reside in the below directory:

/var/lib/postgresql

3. copy the resulting file to the destination server.
You can do this by FTP or even better you could use sshfs to create a directory on which you can access destination server.

Step 4 until step 6 are done on destination server.

4. On destination server delete old database (this may not be necessary, but for testing purpose i wanted to make sure)

[email protected]:sudo -u postgres dropdb name_of_old_database

(*)Check if the database has been deleted by typing “\l” command to show the list of available databases.

[email protected]: sudo -u postgres psql
psql(9.5.13)
postgres=#\l

5. Create new blank database by issuing the below command

[email protected]:sudo -u postgres createdb name_of_new_database

6. Migrate database using below command:

[email protected]:psql name_of_new_database < name_of_dev_db_file.bak

Leave a Reply

Your email address will not be published. Required fields are marked *