First make sure, that the local postgres user has full rights on the Postgres server (trust):
cat pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only local all all trust
# IPv4 local connections: host all all 127.0.0.1/32 trust
DUMP Now we create the full dump (structure and data) of the database DB1:
pg_dump -p 5432 -h localhost -U postgres -Fc -f /tmp/pgdump.sql DB1
And this is for a single table 'table1' in DB1:
pg_dump -Fc -t table1 DB1 > /tmp/table1.dmp
RESTORE To restore the dump several steps need to be done:
1. Create the database DB1 manually if database doesn't exist yet (e.g. if you want to restore the sql-dump on another server)
2. Don't forget to create a role if a special user (not postgres) is the owner of the database.
3. Launch the restore command:
pg_restore -p 5432 -h localhost -U postgres -d DB1 -Fc /tmp/pgdump.sql
|