pg_dump不能导出访问权限 pg_dumpall可以导出访问权限

pg_dump导出，psql导入：

[[email protected] lib]$ ./pg_dump -h localhost -U highgo --port=5866 -f my_dump.sql  --inserts

[[email protected] lib]$ psql 

psql (4.0.0)

Type "help" for help.

highgo=# 

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =c/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =Tc/highgo

(4 rows)

highgo=# drop database test;

DROP DATABASE

highgo=# create database test;

CREATE DATABASE

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =c/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(4 rows)

highgo=# \q

[[email protected] lib]$ pwd

/highgo/hgdb4_server/lib

[[email protected] lib]$ pg_restore -h localhost -p 5866 -U highgo -d test --create --verbose --format custom "/highgo/hgdb4_server/lib/my_dump.sql"

pg_restore: [archiver] did not find magic string in file header

[[email protected] lib]$ psql -h localhost -U highgo -d test < my_dump.sql 

SET

SET

SET

SET

SET

SET

SET

COMMENT

CREATE EXTENSION

COMMENT

SET

SET

SET

CREATE TABLE

ALTER TABLE

ALTER TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

REVOKE

REVOKE

GRANT

GRANT

[[email protected] lib]$ psql

psql (4.0.0)

Type "help" for help.

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =c/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(4 rows)

highgo=# 

========================================================================================

========================================================================================

pg_dumpall导出，psql导入：

 ./pg_dumpall -h localhost -U highgo --port=5866 -f my_dumpall.sql  --inserts

[[email protected] lib]$ psql 

psql (4.0.0)

Type "help" for help.

highgo=# 

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/highgo       +

           |        |          |             |             | highgo=CTc/highgo

(4 rows)

highgo=# drop database test;

DROP DATABASE

highgo=# create database test;

CREATE DATABASE

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(4 rows)

highgo=# CREATE ROLE highgo;

ERROR:  42710: role "highgo" already exists

highgo=# ALTER ROLE highgo WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

ALTER ROLE

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(4 rows)

highgo=# ALTER ROLE test WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md505a671c66aefea124cc08b76ea6d30bb';

ALTER ROLE

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(4 rows)

highgo=# \q

[[email protected] lib]$ psql -h localhost -U highgo -d test < my_dumpall.sql 

SET

SET

SET

ERROR:  42710: role "aaa" already exists

ALTER ROLE

ERROR:  42710: role "highgo" already exists

ALTER ROLE

ERROR:  42710: role "test" already exists

ALTER ROLE

ERROR:  42P04: database "highgo" already exists

REVOKE

REVOKE

GRANT

GRANT

ERROR:  42P04: database "test" already exists

REVOKE

REVOKE

GRANT

GRANT

You are now connected to database "highgo" as user "highgo".

SET

SET

SET

SET

SET

SET

SET

SET

COMMENT

CREATE EXTENSION

COMMENT

SET

SET

SET

ERROR:  42P07: relation "test" already exists

ALTER TABLE

ALTER TABLE

INSERT 0 1

INSERT 0 1

REVOKE

REVOKE

GRANT

GRANT

You are now connected to database "template1" as user "highgo".

SET

SET

SET

SET

SET

SET

SET

SET

COMMENT

CREATE EXTENSION

COMMENT

REVOKE

REVOKE

GRANT

GRANT

You are now connected to database "test" as user "highgo".

SET

SET

SET

SET

SET

SET

SET

SET

CREATE EXTENSION

COMMENT

REVOKE

REVOKE

GRANT

GRANT

[[email protected] lib]$ psql

psql (4.0.0)

Type "help" for help.

highgo=# \l

                               List of databases

   Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 

-----------+--------+----------+-------------+-------------+-------------------

 highgo    | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/highgo        +

           |        |          |             |             | highgo=CTc/highgo

 template1 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =c/highgo

 test      | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 | highgo=CTc/highgo+

           |        |          |             |             | =Tc/highgo

(4 rows)

highgo=#