CODEWITHSUNDEEP
bashpostgresql
Aaryn
Aaryn

Reputation: 1643

Postgres - Pull a copy of an entire DB from a remote server, to local dev machine

Been trying to find the answer to this, but the answers seem to focus on a "push" mentality rather than "pull". Like this:

Copying PostgreSQL database to another server

I have a local VM dev machine. Redhat REHEL. I also have a remote environment, let's name it "integration". I have SSH access to this environment and am in the Sudoers group on the remote server.

What I want is to have a way on my local VM to pull down a complete copy of the database on integration, and push it into a waiting (empty) db. Let's call that DB "int_backup".

This doesn't have to be one command. Happy to bash script multiple commands. But the key here is integration is unaware of my local VM and cannot access it. So the task must be done from the VM side of things.

All suggestions welcome. But installing software on integration is not a likely realistic solution.

Upvotes: 1

Views: 992

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

Perhaps this way (install your public key on the remote's ~postgres/.ssh/authorized_keys file):

ssh postgres@yourremotehost 'pg_dump yourdbname | xz -0' |\
  xzcat | psql yourdbname

Alternatively, add an entry in your remote's /etc/sudoers file to enable your user with the ability to execute sudo -u postgres pg_dump yourdbname passwordlessly, so you can run:

ssh youruser@yourremotehost 'sudo -u postgres pg_dump yourdbname | xz -0' |\
  xzcat | psql yourdbname

In both cases, the dump and the restore will occur simultaneously, and no dump file will be created.

Upvotes: 1

Related Questions