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. bash 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…
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…
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.
- 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.
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.
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.