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

Source: Internet
Author: User
Tags psql
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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.