Examples

• 3

  Backing up one database
  3

  pg_dump -Fc -f DATABASE.pgsql DATABASE
  

  The -Fc selects the "custom backup format" which gives you more power than raw SQL; see pg_restore for more details. If you want a vanilla SQL file, you can do this instead:

  pg_dump -f DATABASE.sql DATABASE
  

  or even

  pg_dump DATABASE > DATABASE.sql
  

  edited Jul 24 '16 at 4:39

  

  
  edit share discuss request improvement

  edit move example delete example
• 3

  Backing up the whole cluster
  3

  $ pg_dumpall -f backup.sql
  

  This works behind the scenes by making multiple connections to the server once for each database and executing pg_dump on it.

  Sometimes, you might be tempted to set this up as a cron job, so you want to see the date the backup was taken as part of the filename:

  $ postgres-backup-$(date +%Y-%m-%d).sql
  

  However, please note that this could produce large files on a daily basis. Postgresql has a much better mechanism for regular backups - WAL archives

  The output from pg_dumpall is sufficient to restore to an identically-configured Postgres instance, but the configuration files in $PGDATA (pg_hba.conf and postgresql.conf) are not part of the backup, so you'll have to back them up separately.

  postgres=# SELECT pg_start_backup('my-backup');
  postgres=# SELECT pg_stop_backup();
  

  To take a filesystem backup, you must use these functions to help ensure that Postgres is in a consistent state while the backup is prepared.

  edited Jul 24 '16 at 4:39

  

  
  edit share discuss request improvement

  edit move example delete example
• 3

  Restoring backups
  3

  psql < backup.sql
  

  A safer alternative uses -1 to wrap the restore in a transaction. The -f specifies the filename rather than using shell redirection.

  psql -1f backup.sql
  

  Custom format files must be restored using pg_restore with the -d option to specify the database:

  pg_restore -d DATABASE DATABASE.pgsql
  

  The custom format can also be converted back to SQL:

  pg_restore backup.pgsql > backup.sql
  

  Usage of the custom format is recommended because you can choose which things to restore and optionally enable parallel processing.

  You may need to do a pg_dump followed by a pg_restore if you upgrade from one postgresql release to a newer one.

  edited Jul 24 '16 at 4:39

  

  
  edit share discuss request improvement

  edit move example delete example
• 2

  Using psql to export data
  2

  Data can be exported using copy command or by taking use of command line options of psql command.

  To Export csv data from table user to csv file:

  psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>
  
  psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv
  

  Here combination of -A and -F does the trick.

  -F is to specify delimiter

  -A or --no-align
  

  Switches to unaligned output mode. (The default output mode is otherwise aligned.)

  edited Jan 18 at 14:56

  

  

  
  edit share discuss request improvement

  edit move example delete example
• 1

  Using Copy to export
  1

  To Copy table to standard o/p

  COPY <tablename> TO STDOUT (DELIMITER '|');

  To export table user to Standard ouput:

  COPY user TO STDOUT (DELIMITER '|');

  To Copy table to file

  COPY user FROM '/home/user/user_data' WITH DELIMITER '|';

  To Copy the output of SQL statement to file

  COPY (sql statement) TO '<filename with path>';

  COPY (SELECT * FROM user WHERE user_name LIKE 'A%') TO '/home/user/user_data';

  To Copy into a compressed file

  COPY user TO PROGRAM 'gzip > /home/user/user_data.gz';

  Here program gzip is executed to compress user table data.

  edited Aug 4 '16 at 15:36

  

  
  edit share discuss request improvement

  edit move example delete example
• 1

  Using Copy to import
  1

  To Copy Data from a CSV file to a table

  COPY <tablename> FROM '<filename with path>';
  

  To insert into table user from a file named user_data.csv placed inside /home/user/:

  COPY user FROM '/home/user/user_data.csv';
  

  To Copy data from pipe separated file to table

  COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
  

  Note: In absence of the option with delimiter, the default delimiter is comma ,

  To ignore header line while importing file

  Use the Header option:

  COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
  

  Note: If data is quoted, by default data quoting characters are double quote. If the data is quoted using any other character use the QUOTE option; however, this option is allowed only when using CSV format.

  edited Aug 12 '16 at 12:27

  

  

  
  edit share discuss request improvement

  edit move example delete example