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?
This question is related to
postgresql
backup
pg-dump
I was trying to run pg_dump from within psql command prompt and I was not able to trace output file anywhere on my ubuntu 20.04 box. I tried finding by find / -name "myfilename.sql"
.
Instead When I tried pg_dump from /home/ubuntu, I found my output file in /home/ubuntu
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.
you can use this command
pg_dump --table=yourTable --data-only --column-inserts yourDataBase > file.sql
you should change yourTable, yourDataBase to your case
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.
If you are on Ubuntu,
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
Source: Stackoverflow.com