我想复制一个生产PostgreSQL数据库到一个开发服务器。最快最简单的方法是什么?
pg_dump the_db_name > the_backup.sql
然后将备份复制到您的开发服务器,使用以下方法进行恢复:
psql the_new_dev_db < the_backup.sql
使用pg_dump,稍后使用psql或pg_restore -取决于您选择- fp或- fc选项来进行pg_dump。
用法示例:
ssh production pg_dump -C -Fp -f dump.sql -U postgres some_database_name scp dump.sql development: rm dump.sql ssh development psql -U postgres -f dump.sql
您不需要创建中间文件。你可以这样做
pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname
或
pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname
使用psql或pg_dump连接到远程主机。
psql
pg_dump
对于大数据库或慢速连接,转储文件和传输压缩文件可能更快。
正如Kornel所说,没有必要转储到中间文件,如果你想压缩工作,你可以使用压缩隧道
pg_dump -C dbname | bzip2 | ssh remoteuser@remotehost "bunzip2 | psql dbname"
pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"
但是这个解决方案还需要在两端都获得一个会话。
注意: pg_dump用于备份,psql用于恢复。所以,回答中的第一个命令是从本地复制到远程,第二个命令是从远程复制到本地。更多-> https://www.postgresql.org/docs/9.6/app-pgdump.html
我努力了很多,最终让我在Rails 4中工作的方法是:
在旧服务器上
sudo su - postgres pg_dump -c --inserts old_db_name > dump.sql
我必须使用postgres linux用户来创建转储。我还必须使用-c来强制在新服务器上创建数据库。——inserts告诉它使用INSERT()语法,否则对我来说是无效的:(
然后,在新服务器上,simpy:
sudo su - postgres psql new_database_name < dump.sql
传输转储。我简单地使用“cat”来打印内容,而不是“nano”来重新创建它复制粘贴内容。
此外,我在两个数据库上使用的角色是不同的,所以我必须在转储中找到-替换所有的所有者名称。
pg_basebackup似乎是更好的方法,特别是对于大型数据库。
pg_basebackup
可以从具有相同或较旧主版本的服务器复制数据库。或# EYZ0:
pg_basebackup适用于相同或较旧版本的服务器,低至9.1。但是WAL流模式(-X stream)仅适用于9.3及以后的服务器版本,当前版本的tar格式模式(--format=tar)仅适用于9.5及以后的服务器版本。
-X stream
--format=tar
对于源服务器,您需要:
listen_addresses = '*'
max_wal_senders = 1
-X fetch
2
wal_level = replica
max_wal_senders > 0
host replication postgres DST_IP/32 trust
pg_hba.conf
DST_IP
pg
更改1、2、3需要重新启动服务器,更改4需要重新加载。
在目标服务器上:
# systemctl stop postgresql@VERSION-NAME postgres$ pg_basebackup -h SRC_IP -U postgres -D VERSION/NAME --progress # systemctl start postgresql@VERSION-NAME
以数据库名称运行此命令,如果要备份,则转储数据库。
pg_dump -U {user-name} {source_db} -f {dumpfilename.sql} eg. pg_dump -U postgres mydbname -f mydbnamedump.sql
现在将这个转储文件scp到您想要复制DB的远程机器。
eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/
在远程机器上,在~/some/文件夹中运行以下命令来恢复DB。
psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql} eg. psql -U postgres -d mynewdb -f mydbnamedump.sql
如果你想在版本之间迁移(例如你更新了postgres,在localhost:5432上运行9.1,在localhost:5434上运行9.3),你可以运行:
pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434
看看迁移文档。
让我分享一个Linux shell脚本,将你的表数据从一个服务器复制到另一个PostgreSQL服务器。
引用自本博客: .
用于PostgreSQL服务器间数据迁移的Linux Bash Shell脚本:
#!/bin/bash psql \ -X \ -U user_name \ -h host_name1 \ -d database_name \ -c "\\copy tbl_Students to stdout" \ | \ psql \ -X \ -U user_name \ -h host_name2 \ -d database_name \ -c "\\copy tbl_Students from stdin"
我只是在迁移数据;请在目标/第二个数据库服务器上创建一个空表。
这是一个实用程序脚本。此外,您还可以修改脚本以使用通用功能,例如为host_name、database_name、table_name等添加参数
host_name
database_name
table_name
转储数据库:pg_dump database_name_name > backup.sql
pg_dump database_name_name > backup.sql
导入数据库:psql db_name < backup.sql
psql db_name < backup.sql
接受的答案是正确的,但如果你想避免交互式输入密码,你可以使用这个:
PGPASSWORD=\{\{export_db_password}} pg_dump --create -h \{\{export_db_host}} -U \{\{export_db_user}} \{\{export_db_name}} | PGPASSWORD=\{\{import_db_password}} psql -h \{\{import_db_host}} -U \{\{import_db_user}} \{\{import_db_name}}
下面是一个使用pg_basebackup的例子
我选择这样做是因为它备份整个数据库集群(用户、数据库等)。
我把这个作为解决方案发布在这里,因为它详细说明了我必须采取的每一步,在阅读这里的其他答案并做更多的研究后,请随意添加建议或改进。
对于Postgres 12和Ubuntu 18.04,我必须做这些操作:
在当前运行数据库的服务器上:
更新pg_hba.conf,为我位于/etc/postgresql/12/main/pg_hba.conf
/etc/postgresql/12/main/pg_hba.conf
添加以下行(将192.168.0.100替换为要将数据库复制到的服务器的IP地址)。
host replication postgres 192.168.0.100/32 trust
更新postgresql.conf,为我位于/etc/postgresql/12/main/postgresql.conf。增加如下一行:
postgresql.conf
/etc/postgresql/12/main/postgresql.conf
重启postgres:
Sudo服务postgresql重启
将数据库集群拷贝到以下主机:
Sudo service postgresql stop Sudo su root Rm -rf /var/lib/postgresql/12/main/* 退出 sudo -u postgres pg_basebackup -h 192.168.0.101 -u postgres -D /var/lib/postgresql/12/main/ .使用实例 Sudo服务postgresql启动
Sudo service postgresql stop
Sudo su root
Rm -rf /var/lib/postgresql/12/main/*
退出
sudo -u postgres pg_basebackup -h 192.168.0.101 -u postgres -D /var/lib/postgresql/12/main/ .使用实例
Sudo服务postgresql启动
大局观-停止服务,删除数据目录中的所有内容(我的在/var/lib/postgreql/12中)。该目录的权限为drwx------,用户组为postgres。我只能用root这样做,甚至不能用sudo -u postgres。我不知道为什么。确保您在要将数据库复制到的新服务器上执行此操作!您正在删除整个数据库集群。
/var/lib/postgreql/12
drwx------
postgres
root
sudo -u postgres
确保将IP地址从192.168.0.101更改为要复制数据库的IP地址。使用pg_basebackup从原始服务器复制数据。启动服务。
192.168.0.101
更新pg_hba.conf和postgresql.conf以匹配原始服务器配置-在您添加replication行和listen_addresses行之前进行任何更改(在我的护理下,我必须添加通过md5本地登录到pg_hba.conf的能力)。
replication
listen_addresses
注意,max_wal_senders和wal_level的注意事项可以在文档中找到。我不需要做任何事。
max_wal_senders
wal_level
如果您更喜欢使用GUI,您可以使用pgAdmin软件。