How to create a backup of a single table in a postgres database?
PostgresqlBackupPg DumpPostgresql Problem Overview
Is there a way to create a backup of a single table within a database using postgres? And how? Does this also work with the pg_dump
command?
Postgresql Solutions
Solution 1 - Postgresql
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
Solution 2 - Postgresql
If you are on Ubuntu,
- Login to your postgres user
sudo su postgres
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
Solution 3 - Postgresql
> 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.
Solution 4 - Postgresql
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.
Solution 5 - Postgresql
you can use this command
pg_dump --table=yourTable --data-only --column-inserts yourDataBase > file.sql
you should change yourTable, yourDataBase to your case
Solution 6 - Postgresql
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.