Shulou(Shulou.com)06/01 Report--

Database import and export is one of the most commonly used functions. PostgreSQL backup tools can use pg_dump and pg_dumpall. You can get its usage through pg_dump-- help. It is not introduced too much here. This paper mainly introduces a problem encountered in the process of using pg_dump and recovery.

1. Question

After exporting using pg_dump-c, import the following errors in the Times through psql:

ERROR: relation "T1" already existsERROR: duplicate key value violates unique constraint "t1_pkey" ERROR: multiple primary keys for table "T1" are not allowed

2. Import and export operations

Pg_dump-U postgres-d yzs-Fa-c-C-f all.sqlpsql < all.sql

3. Problem analysis.

1) drop before rebuilding database when exporting via-c

2) Export create database statement when exporting through-C

3) during each import, although the imported table structure and part of the data are already in the library, and the drop database statement is executed first, there should be no errors such as the table already exists, but this kind of error does occur. It turns out that during import, as long as there is a connection connected to the database,drop statement, it will not be executed successfully, resulting in a failure to clean up the database, and errors such as existing, duplicate keys and multiple primary keys will be reported when the corresponding statement is executed later.

4. Solution

1) when pg_dump exports, there is no option to include if not exists in the exported statement, so that it cannot be created or inserted when it does not exist.

2) make sure to import only when there is no business connection to the database, or to a clean database

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.