Posts Tagged “pg_dump”

Dumping a PostgreSQL database without the data is easy. Issue the below command and it will output the database table structure to a text file that can easily be imported.

pg_dump -U postgres --schema-only DBNAME -f FILENAME.sql

The above can be imported into a new database using the below syntax.

psql -U postgres DBNAME < FILENAME.sql

SQL in a Nutshell (In a Nutshell (O'Reilly)) (Paperback)

By (author): Kevin Kline, Brand Hunt, Daniel Kline


List Price: $44.99 USD
New From: $24.99 USD In Stock
Used from: $16.99 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , ,

Comments No Comments »

You may get the following error when attempting to restore a postgreSQL database. The issue will be that you are attemping to restore a dump of a database using PostgreSQL version 8.4 and anove into a PostgreSQL database that is version 8.3 or below.

ERROR:  syntax error at or near “COLLATE”

To resolve this issue manually edit the dump file by removing two options that are included in your database CREATE statements by default in Postgres 8.4 and above as shown below.

Before Editing:

CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8' COLLATE = 'en_US.UTF-8' CTYPE = 'en_US.UTF-8';
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8' COLLATE = 'en_US.UTF-8' CTYPE = 'en_US.UTF-8';

After Editing:

CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8';
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8';

As you can see the above examples include the creation of two databases from the dump files that were created using pg_dump.

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , ,

Comments No Comments »

Backing up and restoring a postgreSQL database is easy from the CLI. Some people prefer using pgAdmin III however through the GUI and over a network will use a lot more resources so it is recommended to backup and restore via your Linux CLI. Follow the simple steps below to backup and restore one Postgres database from a Linux server to another Linux server.

  1. Backup One PostgreSQL Database: Login to your server and issue the below command from the CLI. In the below syntax change username to the correct username (such as postgres), the database_name to the proper database, and the filename to something meaningful. The filename should end in .sql so you know it is a postgreSQL backup file which will eventually end in .gz once you compress it using gzip. Read the rest of this entry »
DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , ,

Comments 1 Comment »

Below is the command to use when you would like to dump only the data from a postgreSQL table. There are other options to use to dump the schema, multiple tables, exclude certain tables, or backup the entire database but these command line switches will dump only the data from one postgreSQL table.

In the below example replace table_name with the name of the table you are backing up, file_name with the name of the output file (optionally add a path to the file name), and database_name with the name of the database where the table is located.

Read the rest of this entry »

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , ,

Comments 13 Comments »

Backing up and restoring PostgreSQL is fairly easy using pg_dump and psql. One more complicated scenario I have run into is doing a complete database backup with pg_dump and at some point down the road needing to just split out one table and restore it. This can be a pain because of how the pg_dump organizes the .sql file it outputs. I have found the best way to do this is to use pg_dump to backup one table to understand the format of what is needed for a restore. Then search the .sql file that was output from the full pg_dump output and split out the necessary data to restore into one table. Below I provide some examples as well as syntax to use for backing up and/or restoring PostgreSQL using pg_dump and psql.

Read the rest of this entry »

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , ,

Comments 8 Comments »