Restore Postgres database using pg_restore over SSH

Question

I have a database server without much disk space, so I took a backup of the entire db (let's just call it redblue) and saved it locally using the following command (I don't have pg running on my computer):

ssh [email protected] "pg_dump -U postgres redblue -h localhost " \
 >> db_backup_redblue.sql

I'd like to now restore it to another server (1.2.3.4) which contains an older version of "redblue" database - however wanted to ask if this is right before I try it:

ssh [email protected] "pg_restore -U postgres -C redblue" \
<< db_backup_redblue.sql

I wasn't sure if I need to do -C with the name of the db or not?

Will the above command overwrite/restore the remote database with the file I have locally?

Thanks!

You might do better asking this on Database Administrators.

Kenster
– Kenster

2018-08-07 17:38:44 +00:00
Commented Aug 7, 2018 at 17:38 — Kenster
– Kenster, Commented Aug 7, 2018 at 17:38

Laurenz Albe · Accepted Answer · 2018-08-08 06:54:17Z

2

No, that will do nothing good.

You have to start pg_restore on the machine where the dump is. Actually, since this is a plain format dump, you have to use psql rather than pg_restore:

psql -h 1.2.3.4 -U postgres -d redblue -f db_backup_redblue.sql

That requires that there is already an empty database redblue on the target system.

If you want to replace an existing database, you have to use the --clean and --create options with pg_dump.

If you want to use SSL, you'll have to configure the PostgreSQL server to accept SSL connections, see the documentation.

I'd recommend the “custom” format of pg_dump.

answered Aug 8, 2018 at 6:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

volen · Accepted Answer · 2021-04-28 08:23:20Z

1

Of course, you can do this :) Assuming you use ssh keys to authorize user from source host to destination host.

On the source host you do the pg_dump, then pipe through ssh to destination host like this:

pg_dump -C nextcloud | ssh -i .ssh/pg_nextcloud_key [email protected] psql -d template1

Hope that helps ;)

answered Apr 28, 2021 at 8:23

volen

11 bronze badge

Collectives™ on Stack Overflow

Restore Postgres database using pg_restore over SSH

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related