Postgres backup not copying contents

Question

I am working on PostgreSQL database and we have a test server which needs to have the same data set as the production one. For this, I plan to start a daily CRON job in linux and copy the production database along with its contents like tables, rows, columns, sequences.

I checked how to copy databases from one to another, and I used the pg_dump command as I will write it below, but it only copied the database tables, sequences, but not the contents.

What should I do to copy the contents?

pg_dump -C databaseName | ssh -C username@removeHost.com "psql databaseName"

Edit

• So, What I did was I deleted the database which was on test server, created a new empty database and then used the command above, and it worked. So I guess I need to delete the database then only it will overwrite it.
• What should I do to circumvent this behaviour and do a force update of the database, or delete the test server database even if it is use and create a new empty database.

Answer 1

Have you tried to use pg_restore instead of psql ? pg_restore has special arguments for your case: -c -C.

Details here:http://www.postgresql.org/docs/current/static/app-pgrestore.html

An example of a command to dump/transfer/restore a db:

pg_dump -F c databaseName | ssh -C username@removeHost.com 'pg_restore --clean --create -d postgres'

For this command you need an empty db on target instance to connect to. (postgres in example).

database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

If you already have a db on target instance:

pg_dump -F c databaseName | ssh -C username@removeHost.com 'pg_restore --clean -d databaseName'

Similar question: Use pg_dump result as input for pg_restore