Import and export between PG databases

This article will introduce how to import and export the PG database, the main use is the PG comes with pg_dump, Pg_dumpall, Pg_restore, Psql and other commands, version is 9.4 (different versions of pg_dump \ Pg_ The restore option may be somewhat different, please note).


The overall idea of exporting and importing is:

1. Export global objects, such as users, encodings, permissions, etc., resulting in file global-objs.dmp
2. Export objects, structures in each database, such as library statements, users, permissions, encodings, table structures, custom types, and so on, resulting in the [library name]-objs.dmp file, such as Dxm-objs.dmp
3. Export the data in each database, here in two ways, one is all the data in a library (all tables), generate a single file dxm.dmp; one is to export backups for each table in a library, one file per table, named].dmp with the library name]-[table name, such as Dxm-all _types.dmp
4. Establish a global object on the target instance, that is, import global-objs.dmp
5. Import object, which is the content in the Dxm-objs.dmp file
6. Import data, divided into one import dxm.dmp per export, and a file that imports each table separately, such as Dxm-all_types.dmp

The export of data is mainly used Pg_dump tool, can be exported to SQL file, directory mode ("-FD") and custom Format ("-FC") and so on. Where the SQL file is more suitable for smaller instance data, the directory mode can be used in a larger instance because it can be imported and exported in a concurrent way, and the custom format can use concurrency when importing.

Export process

This can be done in the following steps:

1. Export common objects, such as users, permissions, encodings, etc.
2. Export objects on a library, such as tables, type, and so on
3. Export data on a library, such as data from individual tables

Then recovery will be resumed in the same step.

The first step is to export all common objects, including encoded users, permissions, etc.


Output a public part to a file

Copy Code

1. [Email protected] ~]$/pkg/pgsql/bin/pg_dumpall-h 192.168.xx.xx-g-P 5432-f gloable.dmp

All the necessary information, such as structure, object, and code , is exported and used for execution on the new library.


One thing to note is that the user who makes the export must have the appropriate permissions (as in the example above, the user of the operating system that is used by default, that is, DXM, the user who created the instance, with the highest privileges. ), there is no permission to report the following log:

Copy Code

1. [Email protected] ~]$/pkg/pgsql/bin/pg_dumpall-h 192.168.xx.xx-p 5432-u pgtmp
2. ......
3. Pg_dumpall:query Failed:ERROR:permission denied for relation Pg_authid
4. Pg_dumpall:query Was:select OID, Rolname, Rolsuper, Rolinherit, Rolcreaterole, Rolcreatedb, Rolcanlogin, Rolconnlimit, R Olpassword, Rolvaliduntil, Rolreplication, Pg_catalog.shobj_description (OID, ' Pg_authid ') as rolcomment, Rolname = Current_User as Is_current_user from Pg_authid ORDER by 2

The second step is to export the structure and objects on the library


The recommendation here is to separate the import and export of structures, objects, and data, with clearer logic and better positioning of the problem.


To export a structure on a library:

Copy Code

1. [Email protected] ~]$ [backcolor= #ffff00][b]/pkg/pgsql/bin/pg_dump-s-c-v-fdxm-objs.dmp-ddxm-h 192.168.XX.XX-UDXM- P 5432[/b][/backcolor]
2. Password:
3. Pg_dump:reading schemas
4. ......

Because of the "-V" option, more information is printed, which can also be seen from the reading. Options are:

• "-S" option to export all objects in the library without exporting the data
• The "-C" option allows you to export the statements of the repository to the file, and if you build the library manually, you need to remove the option
• "-o" option, if the target library user is different from the source library, then it is best to take this option when exporting, remove the "ALTER ... OWNER to "statement to resolve user non-existent or user permissions issues
• The "-X" option will remove the Grant/invoke statement
• For more options, please refer to "Pg_dump--help"

See what you've exported:

Copy Code

1. [email protected] ~]$ cat Dxm-objs.dmp
2. ......
4. CREATE DATABASE "DXM" with TEMPLATE = template0 ENCODING = ' UTF8 ' lc_collate = ' zh_cn. UTF-8 ' lc_ctype = ' zh_cn. UTF-8 ';
6. ALTER DATABASE "DXM" OWNER to "DXM";
8. \connect "DXM"
9. ......
11. CREATE TABLE Single_types (
12. ID Integer,
13. Name text,
14. Descrps character varying (800),
15. MD5 Bytea
16. );
17. ......

As you can see from the above, it contains only all the objects, no data, and contains the statements that create the database. Once you have these objects, you can import the data again.


Step three, export the data


The exported data supports four formats: SQL file, custom, directory, compressed format.

• SQL file

Only through Psql to recover, the SQL file in the SQL statement redo, slow, but relatively simple, the problem of modifying the SQL file. But with a large amount of data, a single SQL file can reach a large scale and will be difficult to handle.

• Custom formats,

The data is compressed and can be pg_restore for concurrent import. But the output of the same is a single file, for the amount of data is too large, also not very good processing. For medium-sized instances, a simple comparison of the size of the SQL file is as follows (All_types.dmp is a SQL file):

• -rw-rw-r--1 DXM DXM 111736 June 2 18:43 All_types.cs
• -rw-rw-r--1 DXM DXM 1218139 June 2 18:04 all_types.dmp

• How to Catalog

The table of contents is now more suitable for larger instances for the following reasons:

• You can use concurrent export of Pg_dump
• Concurrent imports that can use Pg_restore
• One file per table, not a single file is too large (such as the Way SQL files)
• Have data compression

Export only the data section, excluding the structure part, is the "-a" option.

Directory mode and custom format


The "-FC" option, which takes the form of a custom format, takes up less space and the size of the space is as follows:

Copy Code

1. [Email protected] ~]$/pkg/pgsql/bin/pg_dump-a-v-f dxm.cs-fc-d dxm-h 192.168.xx.xx-u dxm-p 5432
3. -rw-rw-r--1 DXM DXM 51176 June 2 20:33 Single_types.cs
4. -rw-rw-r--1 DXM DXM 588859 June 2 19:50 single_types.dmp

"-FD" option, directory format. When exporting, "-j NUM" will export data in multiple threads, improving performance only under the "-FD" option, as in the following example:

Copy Code

1. [Email protected] ~]$ [backcolor= #ffff00][b]/pkg/pgsql/bin/pg_dump-a-v-j 6-f dxm.folder-fd-d dxm-h 192.168.xx.xxx -U dxm-p 5432[/b][/backcolor]
3. [email protected] ~]$ ll dxm.folder/
4. Total 164
5. -rw-rw-r--1 DXM DXM 50533 June 2 20:40 2866.dat.gz
6. -rw-rw-r--1 DXM DXM 110270 June 2 20:40 2867.dat.gz
7. -rw-rw-r--1 DXM DXM 817 June 2 20:40 toc.dat[font=arial] [/font]

Recovering data


Restore the data, according to the process of export, first restore the object part, and then restore the data.

Recovering objects

First restore the global information, including users, encodings, etc.:

Copy Code

1. [Email protected] ~]$/pkg/pgsql/bin/psql-d postgres-u dxm-h 192.168.xx.xxx-p 5433-f gloable.dmp
2. SET
3. SET
4. SET
5. ......

Second, restore the objects on a library.

If the library is built manually on the target instance, it can be attached to the library, and if not, the Pg_dump option should have "-C" attached to the postgres. The following example, with the "-C" option, allows you to see that the exported file contains the statements created by the library:

Copy Code

1. [[email protected] ~]$[backcolor= #ffff00][b]/pkg/pgsql/bin/psql-d postgres-u dxm-h 192.168.xx.xxx-p 5433-f dxm-objs . Dmp[/b][/backcolor]
2. SET
3. SET
4. SET
5. SET
6. SET
7. SET
8. CREATE DATABASE
9. ALTER DATABASE
10. ......

Recovering data

The custom format and the directory way, when restores the time is supports the multithreading, this has the good effect to the big data quantity. This time does not do too much analysis of performance, only look at the function.


Custom Format:

Copy Code

1. [Email protected] ~]$ [b]/pkg/pgsql/bin/pg_restore-j4-fc-h 192.168.xx.xxx-d dxm-u dxm-p 5433 dxm.cs[/b]

Directory mode:

Copy Code

1. [Email protected] ~]$ [b]/pkg/pgsql/bin/pg_restore-fd-h 192.168.xx.xxx-d dxm-u dxm-p 5433 dxm.folder/[/b]

It can be seen that the use of pg_restore is much simpler. This is mainly because the data and objects are considered separately, so this step is just data. If the exported file contains data and objects, it is also possible to recover only objects, or data, through Pg_restore.
In addition, the format and directory of the way, the data file is not readable, the security of the data is a little more protection.

Summarize

• Pg_dump/pg_restore function is relatively simple and effective
• Pg_dump support for object types is complete, including composite data types, complex types, and more.
• PG also supports other methods (such as copy), and interested students can learn about the following

Report


Source Library structure, data (existing data, can be skipped)


Before you import, export, you need to create some simulation data on the source library. Already have data and instances, can be used already, can skip this step.

To create the original libraries and tables:

Copy Code

1. Create DATABASE "DXM" owner "DXM";
3. Basic types of tables
4. CREATE table if not exists single_types (
5. ID Integer,
6. Name text,
7. Descrps varchar (800),
8. MD5 Bytea
9. );
11. --Create a composite type
12. Create type Branch_desc as (owner text, name text);
14. --a table with arrays, composite types, and other basic types
15. CREATE table if not exists all_types (
16. ID Integer PRIMARY KEY,
17. Name text NOT NULL,
18. Time timestamp NOT NULL,
19. Price Decimal,
20. Num Numeric,
21. Valid Boolean,
22. Profit_per_quarter decimal[],
23. Branch Branch_desc,
24. MD5 Bytea
25. );

Inserting data


The data here is automatically generated, temporarily regardless of logic, that is, the relationship between the data (such as association relationships, etc.). The variety of types is more useful than logical relationships because they are used only for import and export.

Copy Code

2. --Insert 10,000 records
4. INSERT into single_types values (
6. Generate_series (0, 9999),
8. substr (' abcdefghijklmnopqrstuvwxyz ', 1, (random () *):: integer), substr (' abcdefghijklmnopqrstuvwxyz ', 1, (Random () * ):: Integer),
10. E ' \\xDEADBEAFAE346812734989 '
12. );
16. --Insert 10,000 records
18. INSERT into all_types values (
20. Generate_series (0, 9999),
22. substr (' abcdefghijklmnopqrstuvwxyz ', 1, (random () *):: integer),
24. Generate_series (now (), now () + ' 1 week ', ' 1 day '):: Timestamp,
26. (Random () *):: Numeric (10,2),
28. (Random () *):: Numeric (10,0),
30. True
32. ' {100,100,100,100} ',
34. ' ("DXM", "Hangzhou") ',
36. E ' \\xCA9F87A98270197FA9FA '
38. );

Import and export between PG databases