当端口5432被阻塞时，pg_dump 将从远程服务器邮寄数据库

我试图在我们的 DMZ 中的远程服务器上 pg _ dump 一个 SQL 数据库。

  1. 远程服务器上已经没有多少空间了，所以正常的命令运行来本地备份数据库 由于空间问题，pg_dump -C database > sqldatabase.sql.bak无法工作。

  2. 我也无法运行 pg _ dump 命令的其他版本，以便使用以下方法将数据库从远程服务器转储到本地服务器:

    Pg _ dump-C-h Remotehost-U Remoteuser db _ name | psql localhost-U localuser db _ name

因为服务器在我们的 DMZ 中，端口5432被阻塞。我希望看到的是，pg _ dump 数据库并立即将其(ssh 或其他形式)作为文件保存到远程服务器是否可行。 我想说的是: pg_dump -C testdb | ssh admin@ourserver.com | > /home/admin/testdb.sql.bak

有人知道我想达到的目标是否可行吗？

145879 次浏览
小开
最佳答案

You can connect with ssh to your remote server, do with the connect the pg_dump call and send the output back to stdout of local machine.

ssh user@remote_machine "pg_dump -U dbuser -h localhost -C --column-inserts" \
> backup_file_on_your_local_machine.sql
小开

One possible solution - pipe through ssh - has been mentioned.

You also could make your DB server listen on the public inet address, add a hostssl entry for your backup machine to pg_hba.conf, maybe configure a client certificate for security, and then simply run the dump on the client/backup machine with pg_dump -h dbserver.example.com ...

This is simpler for unattended backups.

For the configuration of the connection (sslmode) see also the supported environment variables.

小开

You can try to dump part of the table to a file in your local machine like this (assume your local machine has psql installed):

psql -h ${db_host} -p 5432 -U ${db_user} -d ${db_name} \
-c "\copy (SELECT * FROM my_table LIMIT 10000) to 'some_local_file.csv' csv;"

And you can import the exported csv into another db later like this:

COPY my_table FROM '/path/to/some_local_file.csv' WITH (FORMAT csv);
小开

let's create a backup from remote postgresql database using pg_dump:

pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

later it could be restored at the same remote server using:

sudo -u postgres pg_restore -C mydb_backup.dump

Ex:

pg_dump -h 67.8.78.10 -Fc -o -U myuser mydb > mydb_backup.dump

complete (all databases and objects)

pg_dumpall -U myuser -h 67.8.78.10 --clean --file=mydb_backup.dump

restore from pg_dumpall --clean:

psql -f mydb_backup.dump postgres #it doesn't matter which db you select here

Copied from: https://codepad.co/snippet/73eKCuLx

小开

If you would like to periodically backup a database PostgreSQL that is inside of a container in the remote server to your local host by using pg_dump over ssh, this is useful for you:

https://github.com/omidraha/periodic-pgdump-over-ssh


提交答案