PostgreSQL: Export the schema of a database

Question

My computer broke down but fortunately I backed up the folder C:\Program Files\PostgreSQL.

Now I'm working in a new computer and I would like to import the previous Postgres databases that are stored in the external disk.

I would like to export the schema of a specific database that is located in the backup folder.

The file PostgreSQL\8.3\data\global\pg_database contains information about databases and their OIDs; for example:

"db1" 20012
"db2" 23456

I would like to export the schema of "db1".

There is a folder named "20012" in folder "PostgreSQL\8.3\data\base\20012" that contains a lot of files [500 files].

Is there any way to export the schema of that database?

Note that all of the Postgresql database files are located in an external hard disk and I would like to export the schema of that database in an SQL file, take that file, run it and create the same exact database locally.

Validity

Answer 1

You should take a look at pg_dump:

pg_dump --schema-only databasename

Will dump only the schema to stdout as .sql.

For windows, you'll probably want to call pg_dump.exe. I don't have access to a Windows machine but I'm pretty sure from memory that's the command. See if the help works for you too.

Mella

are you sure that pg_dump is going to work? Cause the files of the database are located in an external disk and not locally... – Validity

Provided you can start the old database server up, yes. pg_dump can connect remotely to a database server as you probably know. I'd attempt to install 8.3 on your new machine and copy over your old database files and start postgres up and see if that works. I thought you were asking more generally about dumping the schema only, my bad. – Mella

i have already installed the 8.3 version of postgresql, copied the data folder to the new installation, but i can't see my old databases... – Validity

@Mella thanks it works. pg_dump -s databasename > schema.sql will outputs the result into schema.sql file – Coquillage

how about custom data types in this case ? – Arboreous

@Arboreous see postgresql.org/docs/9.1/static/xtypes.html I inherited some code that had a native dns resource record custom data type that IIRC worked fine with pg_dump, because the code for the custom data type was written well, – Mella

For me, I found pg_dump $PSQL_CONNECT_STRING -s > myschema.sql useful, where I had set PSQL_CONNECT_STRING as described at postgresql.org/docs/9.2/libpq-connect.html#LIBPQ-CONNSTRING. – Chad

Answer 2

I am running Postgres 9.6 where I had to export a particular schema along with data.

I used the following command:

pg_dump.exe -U username -d databasename -n schemaname > C:\mylocation\mydumpfilename.dmp

If you want only the schema without data, use the switch s instead of n

Below is the pg_dump switch list:

C:\Program Files\PostgreSQL\9.6\bin>pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=SCHEMA          dump the named schema(s) only
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only
  -T, --exclude-table=TABLE    do NOT dump the named table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <[email protected]>.

Autumn

if any one would like to download only schema witch a name for schema should do pg_dump -U username -d databasename -s -n schemaname -f ~/schema_dump.dmp – Aerobic

Answer 3

In Linux you can do like this

pg_dump -U postgres -s postgres > exportFile.dmp

Maybe it can work in Windows too, if not try the same with pg_dump.exe

pg_dump.exe -U postgres -s postgres > exportFile.dmp

Guggenheim

pg_dump: [archiver (db)] connection to database "goldendemon" failed: invalid port number: "-s" – Rosauraroscius

Answer 4

pg_dump -d <databasename> -h <hostname> -p <port> -n <schemaname> -f <location of the dump file>

Please notice that you have sufficient privilege to access that schema. If you want take backup as specific user add user name in that command preceded by -U

Lorinalorinda

pg_dump -s <databasename> -h <hostname> -p <port> -n <schemaname> -f <location of the dump file> Change the flag from -d to -s to get only the schema. This is only to improve this ans other people have also mentioned. – Azide

This will take the entire DB :/ – Rosauraroscius

Answer 5

If you only want the create tables, then you can do pg_dump -s databasename | awk 'RS="";/CREATE TABLE[^;]*;/'

Methodology

Answer 6

For Linux: (data excluded)

• pg_dump -s -t tablename databasename > dump.sql (For a specific table in database)

• pg_dump -s databasename > dump.sql (For the entire database)

Brigandine

(also for mac ;) – Gera

Answer 7

You should use something like this pg_dump --schema=your_schema_name db1, for details take a look here

Brouwer

Answer 8

pg_dump -s databasename -t tablename -U user -h host -p port > tablename.sql

this will limit the schema dump to the table "tablename" of "databasename"

Ealasaid

Answer 9

set up a new postgresql server and replace its data folder with the files from your external disk.

You will then be able to start that postgresql server up and retrieve the data using pg_dump (pg_dump -s for the schema-only as mentioned)

Overgrowth

i replaced the data folder with the old one, then i started the service, opened the pgAdmin III application, clicked on the server icon [i got only 1 server, the previous one]. Shall i create a new server? Cause it doesn't work...I still see the databases i had i created at the beginning...and not the backup ones – Validity

are you sure you popped the files into the correct data folder? I have not used postgresql in Windows so I am not sure about where the data folder would be. I am also not sure if pgAdmin caches anything so you might need to re-connect as well... – Overgrowth

well i just copied the old backup data file in C:\Program Files\PostgreSQL\8.3 and i replaced it with the new one. Also, when i open pgAdmin again, it asks for the password of the old computer, that is a good sign but afterwards in the databases tree i don't see my databases – Validity

Hm... i created a database named "db1" [a database from the old computer], when i click on it the pgAdmin outputs "An error has ocurred: FATAL cache lookup failed for database 20012", what does that mean? – Validity

It is a bit strange that it is not picking up the databases. The user details are stored along with the databases so I wonder if pgAdmin is caching that information. Did you create a new connection? It would also be worth trying the command line client - psql and running the "\l" command to see what databases that returns – Overgrowth

it looks like pgAdmin caches a lot of information. Drop your database connection in pgAdmin and re-create it. That should resolve it if the issue is around caching... – Overgrowth

i deleted all the servers in pgadmin,closed pgadmin and stopped service. Then i started the service, opened pgAdmin and i still see the default server[localhost:5432] again [even though i deleted it previously] and not my backup databases. – Validity

Since i login in pgAdmin with the old password [that is a good sign] why can't i load my back up databases? – Validity

let us continue this discussion in chat – Overgrowth

Answer 10

For example, you can export only the schema of apple database of the user(role) john to backup.sql with -s or --schema-only as shown below. *backup.sql is created if it doesn't exist and my answer explains how to export both schema and data and my answer explains how to export only data:

pg_dump -U john -s apple > backup.sql

Or:

pg_dump -U john --schema-only apple > backup.sql

Then, you need to input a password after running the command above:

Password:

Beslobber

Answer 11

Get base schema from postgres db,

PGPASSWORD=password pg_dump -U username -h hostname -p port -F plain -f mydatabase_backup.sql -s dbname && cat mydatabase_backup.sql

Get data from postgres db,

PGPASSWORD=password pg_dump -U username -h hostname -p port -F plain -f mydatabase_backup.sql -a dbname && cat mydatabase_backup.sql

usage example:

PGPASSWORD=welcome123 pg_dump -U admin -h postgres -p 5432 -F plain -f mydatabase_backup.sql -s auditdatabase && cat mydatabase_backup.sql

Unfriended