SQL script file created with pg_ dump , pg_ restore for restoring from a. Restore a database with psql. If your backup is a plain-text file containing SQL script, then you can restore your database by using PostgreSQL interactive terminal, and running. The idea behind this dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. The general command form to restore a dump is.
Before restoring a database, you need to terminate all connections to that database and prepare the backup file. CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psql command is enough. A dump like that will contain only DDL SQL commands, and will be able to recreate the schema but not the actual data.
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. Dump your PostgreSQL database.
Just as psql allows you to connect to a remote host, pg_ dump can be run from a client computer to back up data on a remote server. Use the -h flag to specify the IP address of your Linode and -p to identify the port on which PostgreSQL is listening: pg_ dump -h 198.
