5

I'm trying to create a backup from PostgreSQL database, but getting the following error: pg_dump: No matching schemas were found

I'm logged in as root and running the command

pg_dump -f db.dump --format=plain --schema=existing_schema --username=userx --host=localhost databasename

  1. I logged in with userx to psql and tried \dt - this gave me information, that schema with name existing_schema is public.
  2. I checked \l to see that databasename is the database name.
  3. Password is correct, otherwise I could not access psql.
  4. Localhost is correct, checked from running processes. Tried the ip-address of the server also, but in this case pg_admin gave an error about the host address.

Output of \dl:

 
                  List of relations

Schema |                Name                 | Type  | Owner
--------+-------------------------------------+-------+-------
 public | existing_schema                    | table | userx
Brad Werth
  • 17,411
  • 10
  • 63
  • 88
stubgo
  • 266
  • 2
  • 5
  • 15
  • "*schema with name existing_schema is public*" that doesn't make sense. Is that schema schema named `public` or is it named `existing_schema`? –  Sep 26 '12 at 10:26
  • That means, that if I enter the command \dt, I'll get the result: Schema | Name | Type | Owner ::: public | existing_schema | table | userx – stubgo Sep 26 '12 at 10:32
  • "*Owner public*" is not something the `\dt` command would display. –  Sep 26 '12 at 10:34
  • It displays Schema : public | Name : existing_schema | Type : table | Owner : userx. Schema, Name, Type and Owner are column headers and public, existing_schema, table, userx are column values in one row. Tried to put them under eachother, but didn't work in comment, sorry. – stubgo Sep 26 '12 at 10:38
  • Can you add the output (copy & paste) to your question? (enclose it in `
    ` tags)
     –  Sep 26 '12 at 10:43
  • Why are you running these as root and not as postgres? – Scott Marlowe Sep 26 '12 at 21:07

5 Answers5

13

You can try with back slash and double quote as metioned here.

sudo -u postgres pg_dump -v Database_Name -t "\"Table_Name\""
ronnefeldt
  • 2,083
  • 23
  • 24
6

The schema's name is public.

existing_schema is the name of a table in the schema public.

It's not clear from your pg_dump commandline what you want to do.

If you want to export all tables from the schema public you need to specify:

--schema=public

If you want to export only the table existing_schema then you need to specify:

--table=existing_schema
3

For future googlers, in my case my schema had dots ("my.schema"), and the proper way to address this is by escaping:

pg_dump -U postgres -d mydatabase --schema \"my.schema\"
juanignaciosl
  • 3,435
  • 2
  • 28
  • 28
0
      \dn list out all the schema in the database

if you want to dump only schema ,

refer

Community
  • 1
  • 1
solaimuruganv
  • 27,177
  • 1
  • 18
  • 23
0

In my case the schema name was upper case and no matter what I tried (-n MYSCHEMA, -n "MYSCHEMA", -n "\"MYSCHEMA\"") it did not work until I renamed the schema to "myschema" in the database. Then I could dump with all of -n MYSCHEMA, -n "MYSCHEMA", and -n "\"MYSCHEMA\"".

It looks like pg_dump internally uses lower case, that means it doesn't quote the schema name given in the command line.

However when calling pg_dump with -n """MYSCHEMA""" it works.

When adding a table name however the option -n did not work anymore for me. If schema and table-name contains capitalized letters, I used this:

pg_dump -f file.txt -t \"MySchema\".\"MyTable\".

Roland
  • 71
  • 5