pg_restore, when run without a database name, outputs a text dump to stdout; you can send that elsewhere with -f or with I/O redirection. pg_restore -f mydatabase.sql mydatabase.dump Note that you must ensure there’s no PGDATABASE environment variable set, or it’ll try to connect to that database.
You have to add the name of a valid database to initially connect to or it will just dump the contents to STDOUT: pg_restore –create –exit-on-error –verbose –dbname=postgres <backup_file>
A quick and dirty way: 1) rename default schema: alter schema public rename to public_save; 2) create new schema as default schema: create schema public; 3) restore data pg_restore -f pub.backup db_temp [and whatever other options] 4) rename schemas according to need: alter schema public rename to temp_schema; alter schema public_save rename to public;
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
The Quick Solution The problem is that it’s trying to perform local peer authentication based on your current username. If you would like to use a password you must specify the hostname with -h. pg_dump dbname -U username -h localhost -F c Explanation This is due to the following in your pg_hba.conf local all all … Read more
Check the installed version(s) of pg_dump: find / -name pg_dump -type f 2>/dev/null My output was: /usr/pgsql-9.3/bin/pg_dump /usr/bin/pg_dump There are two versions installed. To update pg_dump with the newer version: sudo ln -s /usr/pgsql-9.3/bin/pg_dump /usr/bin/pg_dump –force This will create the symlink to the newer version.
Use –table to tell pg_dump what table it has to backup: pg_dump –host localhost –port 5432 –username postgres –format plain –verbose –file “<abstract_file_path>” –table public.tablename dbname