Tutorial by Examples

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 > DATABA...

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 DATABA...

$ 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: $ post...

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 '/h...

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 st...

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&gt...