PostgreSQL database backup and recovery (ultra-fast)

PostgreSQL comes with a client pgadmin, there is a backup, recovery options, but also to restore the database (restore), but recently found that the database slowly large, often error, the backup of the file process error probability that is quite large, Manual adjustment of Grey is often limited. So always look for the perfect backup recovery solution.

In the dream to find him 1100 degrees, she is in the light of the dim place ... In fact PostgreSQL built up a lot of tools, looking for a backup recovery program in which: Pg_dump,psql. These two instructions in the database installation directory, such as my own locally installed, the path shape such as: C:\Program files\postgresql\9.5\, and then into the Bin folder, you will see a lot of EXE files, this is the PostgreSQL built-in tools. Inside will find pg_dump.exe,psql.exe two files . How do we use them?

Usage:

back up the database with the following directives:

Pg_dump-h 164.82.233.54-u postgres databasename > C:\databasename.bak

Start-run the-cmd pop-up DOS console, and then in the console, enter the PostgreSQL installation directory bin:

CD C:\Program Files\postgresql\9.0\bin

Finally, the backup command is executed:

Pg_dump-h 164.82.233.54-u postgres databasename > C:\databasename.bak

Instruction interpretation: As above command, Pg_dump is the backup database instruction, 164.82.233.54 is the IP address of the database (must ensure that the database allows external access to the permissions Oh ~), of course, the local database IP write localhost;postgres is the user name of the database ; DatabaseName is the database name. > means to export to the C:\databasename.bak file, if there is no write path, write Databasename.bak file name alone, then the backup file will be saved in C: \program Files\postgresql\9.0\bin Folder.

Restore the database with the following directives:

Psql-h localhost-u postgres-d DatabaseName <  C:\databasename.bak (test not successful)

Pg_restore.exe--host localhost--port 5432--username "Postgres"--dbname "symbolmcnew"--no-password--verbose "Databas Ename.backup "(Test succeeded)

Instruction explanation: As above command, PSQL is the Restore Database command, localhost is to restore to which database address, of course, you can write the IP address, that is to be able to remotely recover (must ensure that the database allows external access to the permission Oh ~); Postgres is the user who is recovering to which database DatabaseName is the database to which to restore. < means to import the C:\databasename.bak file into the specified database.

All of the above is for Windows, if it is under Linux, will it work?

Still works on Linux. One notable thing is that if you go directly to the PostgreSQL installation directory bin and execute the command, there may be no pg_dump,psql found, we can do this:

Backup :

/opt/postgresql/9.5/bin/pg_dump-h 164.82.233.54-u postgres databasename > Databasename.bak

Recovery:

/opt/postgresql/9.5/bin/psql-h localhost-u postgres-d DatabaseName < Databasename.bak

Transferred from: http://www.cnblogs.com/xiaofoyuan/p/5253332.html#3715264

Original address: http://www.open-open.com/lib/view/open1385448336182.html

PostgreSQL database Backup and recovery (ultra-fast)