pg_restore man page on Raspbian

Man page or keyword search:  
man Server   8174 pages
apropos Keyword Search (all sections)
Output format
Raspbian logo
[printable version]

PG_RESTORE(1)		PostgreSQL Client Applications		 PG_RESTORE(1)

NAME
       pg_restore  - restore a PostgreSQL database from an   archive file cre‐
       ated by pg_dump

SYNOPSIS
       pg_restore [ option... ]	 [ filename ]

DESCRIPTION
       pg_restore is a utility for restoring a PostgreSQL database from an ar‐
       chive  created  by  pg_dump(1) in one of the non-plain-text formats. It
       will issue the commands necessary to reconstruct the  database  to  the
       state  it was in at the time it was saved. The archive files also allow
       pg_restore to be selective about what is restored, or even  to  reorder
       the items prior to being restored. The archive files are designed to be
       portable across architectures.

       pg_restore can operate in two modes.  If a database name is  specified,
       pg_restore  connects  to	 that  database	 and restores archive contents
       directly into the database. Otherwise, a script containing the SQL com‐
       mands  necessary	 to  rebuild  the database is created and written to a
       file or standard output. This script output is equivalent to the	 plain
       text  output  format  of	 pg_dump.  Some of the options controlling the
       output are therefore analogous to pg_dump options.

       Obviously, pg_restore cannot restore information that is not present in
       the  archive  file.  For	 instance,  if	the archive was made using the
       ``dump data as INSERT commands'' option, pg_restore will not be able to
       load the data using COPY statements.

OPTIONS
       pg_restore accepts the following command line arguments.

       filename
	      Specifies	 the  location of the archive file to be restored.  If
	      not specified, the standard input is used.

       -a

       --data-only
	      Restore only the data, not the schema (data definitions).

       -c

       --clean
	      Clean (drop) database objects before recreating them.

       -C

       --create
	      Create the database before restoring into it. (When this	option
	      is  used,	 the  database named with -d is used only to issue the
	      initial CREATE DATABASE command. All data is restored  into  the
	      database name that appears in the archive.)

       -d dbname

       --dbname=dbname
	      Connect  to  database dbname and restore directly into the data‐
	      base.

       -e

       --exit-on-error
	      Exit if an error is encountered while sending  SQL  commands  to
	      the  database. The default is to continue and to display a count
	      of errors at the end of the restoration.

       -f filename

       --file=filename
	      Specify output file for generated script,	 or  for  the  listing
	      when used with -l. Default is the standard output.

       -F format

       --format=format
	      Specify  format  of  the archive. It is not necessary to specify
	      the format, since pg_restore will determine the format automati‐
	      cally. If specified, it can be one of the following:

	      t

	      tar    The archive is a tar archive.

	      c

	      custom The archive is in the custom format of pg_dump.

       -i

       --ignore-version
	      A deprecated option that is now ignored.

       -I index

       --index=index
	      Restore definition of named index only.

       -j number-of-jobs

       --jobs=number-of-jobs
	      Run  the	most  time-consuming parts of pg_restore — those which
	      load data, create indexes, or create constraints — using	multi‐
	      ple  concurrent  jobs.  This  option can dramatically reduce the
	      time to restore a large database to a server running on a multi-
	      processor machine.

	      Each  job is one process or one thread, depending on the operat‐
	      ing system, and uses a separate connection to the server.

	      The optimal value for this option depends on the hardware	 setup
	      of  the  server,	of  the	 client,  and of the network.  Factors
	      include the number of CPU cores and the disk setup. A good place
	      to  start	 is  the number of CPU cores on the server, but values
	      larger than that can also lead to faster restore times  in  many
	      cases. Of course, values that are too high will lead to decreas‐
	      ing performance because of thrashing.

	      Only the custom archive format is supported  with	 this  option.
	      The  input  file	must  be  a  regular file (not, for example, a
	      pipe). This option is ignored when emitting a script rather than
	      connecting  directly  to	a database server. Also, multiple jobs
	      cannot be used together with the option --single-transaction.

       -l

       --list List the contents of the archive. The output of  this  operation
	      can  be  used  as input to the -L option. Note that if filtering
	      switches such as -n or -t are used with -l, they	will  restrict
	      the items listed.

       -L list-file

       --use-list=list-file
	      Restore  only  those  archive  elements that are listed in list-
	      file, and restore them in the order they	appear	in  the	 file.
	      Note  that  if filtering switches such as -n or -t are used with
	      -L, they will further restrict the items restored.

	      list-file is normally created by editing the output of a	previ‐
	      ous  -l  operation.  Lines can be moved or removed, and can also
	      be commented out by placing a semicolon (;) at the start of  the
	      line. See below for examples.

       -n namespace

       --schema=schema
	      Restore  only  objects that are in the named schema. This can be
	      combined with the -t option to restore just a specific table.

       -O

       --no-owner
	      Do not output commands to set ownership of objects to match  the
	      original database.  By default, pg_restore issues ALTER OWNER or
	      SET SESSION AUTHORIZATION statements to set ownership of created
	      schema  elements.	 These statements will fail unless the initial
	      connection to the database is made by a superuser (or  the  same
	      user  that owns all of the objects in the script).  With -O, any
	      user name can be used for the initial connection, and this  user
	      will own all the created objects.

       --no-tablespaces
	      Do not output commands to select tablespaces.  With this option,
	      all objects will be  created  in	whichever  tablespace  is  the
	      default during restore.

       -P function-name(argtype [, ...])

       --function=function-name(argtype [, ...])
	      Restore  the  named function only. Be careful to spell the func‐
	      tion name and arguments exactly  as  they	 appear	 in  the  dump
	      file's table of contents.

       -R

       --no-reconnect
	      This option is obsolete but still accepted for backwards compat‐
	      ibility.

       -s

       --schema-only
	      Restore only the schema (data definitions), not the data	(table
	      contents). Sequence current values will not be restored, either.
	      (Do not confuse this with the --schema option,  which  uses  the
	      word ``schema'' in a different meaning.)

       -S username

       --superuser=username
	      Specify  the superuser user name to use when disabling triggers.
	      This is only relevant if --disable-triggers is used.

       -t table

       --table=table
	      Restore definition and/or data of named table only.

       -T trigger

       --trigger=trigger
	      Restore named trigger only.

       -v

       --verbose
	      Specifies verbose mode.

       -x

       --no-privileges

       --no-acl
	      Prevent restoration  of  access  privileges  (grant/revoke  com‐
	      mands).

       --disable-triggers
	      This  option  is	only  relevant	when  performing  a  data-only
	      restore.	It instructs pg_restore to execute commands to	tempo‐
	      rarily  disable  triggers on the target tables while the data is
	      reloaded. Use this if you have referential integrity  checks  or
	      other triggers on the tables that you do not want to invoke dur‐
	      ing data reload.

	      Presently, the commands emitted for --disable-triggers  must  be
	      done  as superuser. So, you should also specify a superuser name
	      with -S, or preferably run pg_restore as a PostgreSQL superuser.

       --use-set-session-authorization
	      Output SQL-standard SET SESSION AUTHORIZATION  commands  instead
	      of  ALTER	 OWNER	commands  to  determine object ownership. This
	      makes the dump more standards compatible, but depending  on  the
	      history of the objects in the dump, might not restore properly.

       --no-data-for-failed-tables
	      By  default, table data is restored even if the creation command
	      for the table failed (e.g., because it  already  exists).	  With
	      this option, data for such a table is skipped.  This behavior is
	      useful if the target database already contains the desired table
	      contents.	 For  example,	auxiliary tables for PostgreSQL exten‐
	      sions such as PostGIS might already  be  loaded  in  the	target
	      database;	 specifying this option prevents duplicate or obsolete
	      data from being loaded into them.

	      This option is effective only when  restoring  directly  into  a
	      database, not when producing SQL script output.

       -1

       --single-transaction
	      Execute  the  restore as a single transaction (that is, wrap the
	      emitted commands in BEGIN/COMMIT). This ensures that either  all
	      the  commands  complete successfully, or no changes are applied.
	      This option implies --exit-on-error.

       pg_restore also accepts the following command line arguments  for  con‐
       nection parameters:

       -h host

       --host=host
	      Specifies	 the  host  name of the machine on which the server is
	      running. If the value begins with a slash, it  is	 used  as  the
	      directory	 for the Unix domain socket. The default is taken from
	      the PGHOST environment variable, if  set,	 else  a  Unix	domain
	      socket connection is attempted.

       -p port

       --port=port
	      Specifies	 the  TCP port or local Unix domain socket file exten‐
	      sion on which the server is listening for connections.  Defaults
	      to  the  PGPORT  environment  variable, if set, or a compiled-in
	      default.

       -U username

       --username=username
	      User name to connect as.

       -w

       --no-password
	      Never issue a password prompt. If the server  requires  password
	      authentication  and  a  password is not available by other means
	      such as a .pgpass file, the connection attempt will  fail.  This
	      option  can be useful in batch jobs and scripts where no user is
	      present to enter a password.

       -W

       --password
	      Force pg_restore to prompt for a password before connecting to a
	      database.

	      This  option is never essential, since pg_restore will automati‐
	      cally prompt for a  password  if	the  server  demands  password
	      authentication.	However,  pg_restore  will  waste a connection
	      attempt finding out that the server wants a password.   In  some
	      cases  it	 is  worth  typing  -W	to  avoid the extra connection
	      attempt.

       --role=rolename
	      Specifies a role name to be used to perform the  restore.	  This
	      option  causes  pg_restore  to issue a SET ROLE rolename command
	      after connecting to the database. It is useful when the  authen‐
	      ticated  user  (specified	 by  -U)  lacks	 privileges  needed by
	      pg_restore, but can switch to a role with the  required  rights.
	      Some  installations have a policy against logging in directly as
	      a superuser, and use of this option allows restores to  be  per‐
	      formed without violating the policy.

ENVIRONMENT
       PGHOST

       PGOPTIONS

       PGPORT

       PGUSER Default connection parameters

       This utility, like most other PostgreSQL utilities, also uses the envi‐
       ronment variables supported by libpq (see in the documentation).

DIAGNOSTICS
       When a direct database connection is specified  using  the  -d  option,
       pg_restore  internally  executes	 SQL  statements. If you have problems
       running pg_restore, make sure you are able to select  information  from
       the  database using, for example, psql(1). Also, any default connection
       settings and environment variables used by the libpq front-end  library
       will apply.

NOTES
       If your installation has any local additions to the template1 database,
       be careful to load the output of pg_restore into a  truly  empty	 data‐
       base;  otherwise	 you are likely to get errors due to duplicate defini‐
       tions of the added objects. To make an empty database without any local
       additions, copy from template0 not template1, for example:

       CREATE DATABASE foo WITH TEMPLATE template0;

       The limitations of pg_restore are detailed below.

       · When restoring data to a pre-existing table and the option --disable-
	 triggers is used, pg_restore emits commands to	 disable  triggers  on
	 user  tables  before  inserting  the  data then emits commands to re-
	 enable them after the data has	 been  inserted.  If  the  restore  is
	 stopped in the middle, the system catalogs might be left in the wrong
	 state.

       · pg_restore cannot restore large  objects  selectively,	 for  instance
	 only  those  for  a  specific	table.	If  an	archive contains large
	 objects, then all large objects will be restored, or none of them  if
	 they are excluded via -L, -t, or other options.

       See  also  the  pg_dump(1)  documentation for details on limitations of
       pg_dump.

       Once restored, it is wise to run ANALYZE on each restored table so  the
       optimizer  has  useful  statistics; see in the documentation and in the
       documentation for more information.

EXAMPLES
       Assume we have dumped a database called mydb into a custom-format  dump
       file:

       $ pg_dump -Fc mydb > db.dump

       To drop the database and recreate it from the dump:

       $ dropdb mydb
       $ pg_restore -C -d postgres db.dump

       The database named in the -d switch can be any database existing in the
       cluster; pg_restore only uses it to issue the CREATE  DATABASE  command
       for  mydb. With -C, data is always restored into the database name that
       appears in the dump file.

       To reload the dump into a new database called newdb:

       $ createdb -T template0 newdb
       $ pg_restore -d newdb db.dump

       Notice we don't use -C, and instead connect directly to the database to
       be  restored  into.  Also note that we clone the new database from tem‐
       plate0 not template1, to ensure it is initially empty.

       To reorder database items, it is first necessary to dump the  table  of
       contents of the archive:

       $ pg_restore -l db.dump > db.list

       The listing file consists of a header and one line for each item, e.g.:

       ;
       ; Archive created at Fri Jul 28 22:28:36 2000
       ;     dbname: mydb
       ;     TOC Entries: 74
       ;     Compression: 0
       ;     Dump Version: 1.4-0
       ;     Format: CUSTOM
       ;
       ;
       ; Selected TOC Entries:
       ;
       2; 145344 TABLE species postgres
       3; 145344 ACL species
       4; 145359 TABLE nt_header postgres
       5; 145359 ACL nt_header
       6; 145402 TABLE species_records postgres
       7; 145402 ACL species_records
       8; 145416 TABLE ss_old postgres
       9; 145416 ACL ss_old
       10; 145433 TABLE map_resolutions postgres
       11; 145433 ACL map_resolutions
       12; 145443 TABLE hs_old postgres
       13; 145443 ACL hs_old

       Semicolons start a comment, and the numbers at the start of lines refer
       to the internal archive ID assigned to each item.

       Lines in the file can be commented out,	deleted,  and  reordered.  For
       example:

       10; 145433 TABLE map_resolutions postgres
       ;2; 145344 TABLE species postgres
       ;4; 145359 TABLE nt_header postgres
       6; 145402 TABLE species_records postgres
       ;8; 145416 TABLE ss_old postgres

       could  be  used	as input to pg_restore and would only restore items 10
       and 6, in that order:

       $ pg_restore -L db.list db.dump

SEE ALSO
       pg_dump(1), pg_dumpall(1), psql(1)

Application			  2013-04-02			 PG_RESTORE(1)
[top]

List of man pages available for Raspbian

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net