been trying find answer this, answers seem focus on "push" mentality rather "pull". this:
copying postgresql database server
i have local vm dev machine. redhat rehel. have remote environment, let's name "integration". have ssh access environment , in sudoers group on remote server.
what want have way on local vm pull down complete copy of database on integration, , push waiting (empty) db. let's call db "int_backup".
this doesn't have 1 command. happy bash script multiple commands. key here integration unaware of local vm , cannot access it. task must done vm side of things.
all suggestions welcome. installing software on integration not realistic solution.
perhaps way (install public key on remote's ~postgres/.ssh/authorized_keys file):
ssh postgres@yourremotehost 'pg_dump yourdbname | xz -0' |\ xzcat | psql yourdbname alternatively, add entry in remote's /etc/sudoers file enable user ability execute sudo -u postgres pg_dump yourdbname passwordlessly, can run:
ssh youruser@yourremotehost 'sudo -u postgres pg_dump yourdbname | xz -0' |\ xzcat | psql yourdbname in both cases, dump , restore occur simultaneously, , no dump file created.
