Postgresql9.2 supports automatic backup under the command line by backing up and restoring pg_dump in Windows

Pg_dump implements automatic backup under the command line

This project uses the pg_dump.exe tool to back up the PostgreSQL database. The previous command is as follows:

Pg_dump -- Host localhost -- Port 5432 -- username

"S" -- format custom -- blobs -- verbose -- file "C: \ 20111209pm-xfserver.backup" Pipeline

However, during the test, it is found that the command line needs to enter password: to continue the execution, so that automatic backup cannot be achieved, and the parameter is not like setting username -- password, this parameter is not provided for security purposes.

 

Later, after reading the information, I found a method by adding a line before the command:

Set pgpassword = <password>

You can.

 

In this way, the command is executed in a batch file, and the entire file looks like this:

Backup:

@ Echo off
C:

Cd c: \ Program Files \ PostgreSQL \ 9.2 \ bin

Set pgpassword = dsideal

Pg_dump -- Host localhost -- Port 5432 -- username "s" -- format custom -- blobs -- verbose -- file "C: \ % Date :~ 0, 4%-% Date :~ 5, 2%-% Date :~ 8, 2%-% time :~ 0, 2%-% time :~ 3,2%-% time :~ 6, 2%. Dump "dsideal
Pause

 

Restore

Restore database instance dsideal

To demonstrate the experiment, we must first Delete the database: dsideal.

Then immediately:

Cd c: \ Program Files \ PostgreSQL \ 9.2 \ bin

Psql-u Postgres

You can perform the following operations at a fast speed:

Drop database dsideal;

If you are lucky, the deletion is successful. Otherwise, you need to restart the Windows service again.

 

Command for restoration:

pg_restore.exe -hlocalhost -Upostgres -ddsideal -v "C:\2013-04-22-15-57-52.dump"

Of course, the premise is that your database dsideal needs to be created in advance, and it is not responsible for creating the database.

 

If it is in a Linux environment, it is simple:

The other is to query the service processes that use the database, then kill the service process, and finally Delete the database.

Figure 1

In Figure 1, the Database "test_db" cannot be deleted because another user is using it, what we need to do is to use "PS-Ef | grep test_db | grep-V grep" to find the service process using it, as shown in figure 2.

Figure 2

In this case, you only need to kill the three service processes in figure 2 and then delete the database, as shown in figure 3 and figure 4.

Figure 3

 

This project uses the pg_dump.exe tool to back up the PostgreSQL database. The previous command is as follows:

Pg_dump -- Host localhost -- Port 5432 -- username

"S" -- format custom -- blobs -- verbose -- file "C: \ 20111209pm-xfserver.backup" Pipeline

However, during the test, it is found that the command line needs to enter password: to continue the execution, so that automatic backup cannot be achieved, and the parameter is not like setting username -- password, this parameter is not provided for security purposes.

 

Later, after reading the information, I found a method by adding a line before the command:

Set pgpassword = <password>

You can.

 

In this way, the command is executed in a batch file, and the entire file looks like this:

Backup:

@ Echo off
C:

Cd c: \ Program Files \ PostgreSQL \ 9.2 \ bin

Set pgpassword = dsideal

Pg_dump -- Host localhost -- Port 5432 -- username "s" -- format custom -- blobs -- verbose -- file "C: \ % Date :~ 0, 4%-% Date :~ 5, 2%-% Date :~ 8, 2%-% time :~ 0, 2%-% time :~ 3,2%-% time :~ 6, 2%. Dump "dsideal
Pause

 

Restore

Restore database instance dsideal

To demonstrate the experiment, we must first Delete the database: dsideal.

Then immediately:

Cd c: \ Program Files \ PostgreSQL \ 9.2 \ bin

Psql-u Postgres

You can perform the following operations at a fast speed:

Drop database dsideal;

If you are lucky, the deletion is successful. Otherwise, you need to restart the Windows service again.

 

Command for restoration:

pg_restore.exe -hlocalhost -Upostgres -ddsideal -v "C:\2013-04-22-15-57-52.dump"

Of course, the premise is that your database dsideal needs to be created in advance, and it is not responsible for creating the database.

 

If it is in a Linux environment, it is simple:

The other is to query the service processes that use the database, then kill the service process, and finally Delete the database.

Figure 1

In Figure 1, the Database "test_db" cannot be deleted because another user is using it, what we need to do is to use "PS-Ef | grep test_db | grep-V grep" to find the service process using it, as shown in figure 2.

Figure 2

In this case, you only need to kill the three service processes in figure 2 and then delete the database, as shown in figure 3 and figure 4.

Figure 3