How to export data from postgresql and reload-优快云博客

本文链接：https://blog.youkuaiyun.com/high_java/article/details/84087356

本文介绍如何使用PostgreSQL内置的pg_dump命令在Ubuntu和Windows服务器间高效同步数据，通过导出和导入SQL文件实现数据一致性。适用于小型数据集的同步，不建议用于大型数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.