How to create a backup of a single table in a postgres database?

Is there a way to create a backup of a single table within a d开发者_运维百科atabase using postgres? And how? Does this also work with the pg_dump command?

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

If you are on Ubuntu,

1. Login to your postgres user sudo su postgres
2. pg_dump -d <database_name> -t <table_name> > file.sql

Make sure that you are executing the command where the postgres user have write permissions (Example: /tmp)

Edit

If you want to dump the .sql in another computer, you may need to consider skipping the owner information getting saved into the .sql file.

You can use pg_dump --no-owner -d <database_name> -t <table_name> > file.sql

pg_dump -h localhost -p 5432 -U postgres -d mydb -t my_table > backup.sql

You can take the backup of a single table but I would suggest to take the backup of whole database and then restore whichever table you need. It is always good to have backup of whole database.

9 ways to use pg_dump

If you prefer a graphical user interface, you can use pgAdmin III (Linux/Windows/OS X). Simply right click on the table of your choice, then "backup". It will create a pg_dump command for you.

you can use this command

pg_dump --table=yourTable --data-only --column-inserts yourDataBase > file.sql

you should change yourTable, yourDataBase to your case

As an addition to Frank Heiken's answer, if you wish to use INSERT statements instead of copy from stdin, then you should specify the --inserts flag

pg_dump --host localhost --port 5432 --username postgres --format plain --verbose --file "<abstract_file_path>" --table public.tablename --inserts dbname

Notice that I left out the --ignore-version flag, because it is deprecated.

Use the following command to get the compressed version of the table dump :

pg_dump -h localhost -p 5432 -U <username> -d <dbname> -t <tablename> -Fc -f backup.out

Here is how I do it.

pg-dump -h localhost -U postgres -p 5432 -t table database  > path/to/store/name.sql

and run it like this

 psql -h localhost -U postgres -p 5432 database < path/to/store/name.sql