Today encountered a database was damaged to restore the database problem, look up to see a lot of data, finally solved, and then make a record

1. Database postgres damage recovery method (This method is also suitable for retrieving data after a system crash)

If the system crashes and data needs to be retrieved, ensure that the Data folder in the PostgreSQL installation directory exists

The core principles are as follows: Back up data, reinstall databases of the same version as those of the original version, including 32-bit or 64-bit databases, replace the PostgreSql Data folder (rename the original data folder), and restart the PostgreSql service. (Remember to read every word.)

1. Find the data file to back up the database (make sure it is complete)

2. Install the database of the same version

3. To stop the Postgres service, enter services. MSC in the running dialog box and close the Postgres service in the start dialog box, or enter CMD in the CMD dialog box

Net Stop postgresqL-9.3 (here is the name of the service, change according to your actual situation)

4. Shut down the PostGRE process in the Task Manager if it exists (it doesn’t)

5. To remove the Postgres service, enter CMD (run as administrator)

CD “d:\Program Files\PostgreSQL\9.3\bin” Execution directory Go to the bin directory in the Postgre installation directory, and enter the following command

Pg_ctl unregister -n “postgresqL-9.3” pg_ctl unregister -n “postgresqL-9.3”

5. Ensure that the Postgre process is not in the task Manager, change the data file in the Postgres installation directory to data1, and copy the data file to be backed up in 1 to the current Postgres directory.

6. Recreate the Postgres service. Run CMD (run as administrator) to “d:\Program Files\PostgreSQL\9.3\bin”

Pg_ctl register -n “postgres” -d “D :\Program Files\PostgreSQL\9.3\data” pg_ctl register -n “postgres” -d” D :\Program Files\PostgreSQL\9.3\data”

7. Start the service. Enter Net start postgres in CMD or open the service catalog and click Start

​

8. Open pGADmin3, click Connect server host, enter 127.0.0.1, enter the password before postgres, and then connect to the previous server

2. Common Postgres data backup and recovery methods

1. Data backup:

D:\PostgreSQL\10\bin\pg_dump.exe --file "D:\vvv.tar" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --role "postgres" --format=t --blobs --encoding "UTF8" "gis"
Copy the code

2. Data Recovery

D:\PostgreSQL\10\bin\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --dbname "gis2"  --verbose "D:\aa.tar"Copy the code

3. Batch backup

@ECHO OFF @setlocal enableextensions @cd /d "%~dp0" SET PGPATH=D:\PostgreSQL\10\bin\ SET SVPATH=E:\DatabaseBackup\ SET PRJDB=gis SET DBUSR=postgres SET DBDUMP PRJDB = % % _ % date: ~ 0, 4 date: % % % % ~ 5, 2 date: ~ 8, 2 - % % time: ~ 0, 2 _ % % time: ~ % _ % time: 3, 2 ~ 6, 2%. Tar @ ECHO OFF PGPATH % % pg_dump. The exe --file "%SVPATH%%DBDUMP%" --host "localhost" --port "5432" --username "%DBUSR%" --no-password --verbose --role "%DBUSR%"  --format=t --blobs --encoding "UTF8" "gis" echo Backup Taken Complete %SVPATH%%DBDUMP% pauseCopy the code

Three,Postgresql service starts and then stops

Error 1: The service cannot be restarted or started and then stopped. The error message is as follows

​

View the system log: Open the computer management ====> view the application log information, it can be seen that the log configuration is incorrect

The logs are as follows:

​

​

Solution:

We see that it’s related to the postgressql.conf file and it’s garbled

Copy the postgressql.conf file from the data folder where Postgres is installed and restart the service

Mistake 2:

If an error occurs in the log file, the following symptom occurs

​

Solution: Find the postgresql.conf file and make the following changes

​

4. FAQ summary of Postgres

1. Postgres cannot import SHP data

Postgis ShapeFile and DBF Loader plug-ins must be installed.

You can useThe installation wizard can also be downloaded from the PostGIS website (www.postgis.net/).

​

After the installation, import the following figure

​

2. After Postgres is installed, other machines cannot connect to the database

Add the following code to the last line of the folder \PostgreSQL\9.3\data\pg_hba.conf

Host All All 192.168.0.1/16 MD5Copy the code

3. If PostGREs cannot import spatial data into the tables, install the PostGIS extension as follows: Create an extension for the database, name it PostGIS, and save it

​​

You can also use the command line

CREATE EXTENSION postgis;
Copy the code

4. Relevant addresses

If you want to download and install other versions, you can find them at:

PostgreSQL’s official website: www.postgresql.org/download/wi…

PostGIS website: download.osgeo.org/postgis/win…

Focus on

If you have any questions, please comment below

For more learning knowledge, please follow the wechat official account or CSDN blog: Northwest Code farmer

​