PostgreSQL: pg_dump for a single table

Question

I'm beginner with PostgreSQL and doing backups using:

sudo -u postgres pg_dumpall > /~/postgreBackup.SQL

Works fine! Now I want to backup a single table "TableName" in a scheme "SchemeName" and tried

sudo -u postgres pg_dump --table "SchemaName"."TableName" > /~/Dummy.SQL

pg_dump: no matching tables were found

How to get it working?

Eolian

What are the actual table names (please quote verbatim from the successful dump), and what is the exact --table parameter that you used in the second command? – Idelson

You don't specifiy the database in your command. Also, check your schema and table names with psql -U postgres -d $Your_Database -c '\d'. – Hydrophilic

Sorry, I'm totally lost: The Database is "myDatabase", it has the scheme "public" and two schemes for working "myScheme" and "SandBox". Within "MyScheme" I have the table "TableName 01" tht is desired to be dumped. – Eolian

psql -U postgres -d $"myDatabase" -c '\d' results in List of relations for scheme public ... – Eolian

Answer 1

When you have case sensitive table and schema name you have to do the proper quoting of a table name. The below command should work fine as I have successfully executed it at my end.

Please make sure you are using the correct case sensitive name of database, schema and table in this command.

./pg_dump --dbname="myDatabase" --host=localhost --port=5432 --username=postgres  --table='"MyScheme"."TableName 01"' --file=Dummy

OR

./pg_dump --dbname="myDatabase" --host=localhost --port=5432 --username=postgres  --table='"MyScheme"."TableName 01"' > ~/Dummy.SQL

Invite