How to export data from postgresql and reload

Well, Recently I need to work on ubuntu and windows temporately. There are two postgresql servers that are running on ubuntu and windows respectively. But I need the data on both servers is consistent, so I need export the data then reload it after switching workstation. 

 

After doing some research, I found one build-in command in postgresql server does the tricks. pg_dump and pg_dumpall. Here I only use pg_dump to accomplish my task.

 

Export:

 

pg_dump -c scportal -h localhost -U ethanz > /home/ethanz/database_dump1.sql

 

 

-c
--clean
Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Restore might generate some harmless errors.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

 

That is exactly what I need, connect to my local server, export the sql.

 

Then I can put the exported sql to svn or wherever. After switching the workstation, I'm able to synchrnize the data from the output sql file

 

Load:

ethanz@ethanz-U:~$ psql -d scportal -h localhost -U ethanz -f /home/ethanz/database_dump1.sql

 Then postgresql will automatically drop the database, index, table, sequence and recreate them then load the data. I think it's a good solution to resynchronize small buntch of data. It's not suitable to huge database.

 

 

This article is only for reference.