postgresql copy命令 pgsql copy from

介绍

pg_dump备份

只能备份单个数据库，不会导出角色和表空间相关的信息

• -F c 备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原
• -F p 备份为文本,大库不推荐
  更多介绍 ：

复制数据库

新建abc数据库

[postgres@localhost ~]$ psql -c"create database abc"    ----创建一个空库

CREATE DATABASE

[postgres@localhost ~]$ pg_dump portal |psql abc     

                 ----以流的方式，将portal数据库中的数据复制到abc库中

查看拷贝的abc数据库的大小。

postgres=# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
  datname  |  size   
-----------+---------
 postgres  | 7363096
 wang      | 7248408
 template1 | 7135748
 template0 | 7135748
 abc       | 7248408
(5 rows)

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname=‘ga_zj_taizhou’ AND pid<>pg_backend_pid();

将PostgreSQL数据库复制到另一台服务器

#将需要复制的数据库，打包成sql格式。
pg_dump the_db_name > the_backup.sql

#然后将备份复制到开发服务器，使用以下命令恢复：
psql the_new_dev_db < the_backup.sql
（需要先创建the_new_dev_db数据库 create database the_new_dev_db）

或者导入数据库数据

\i  /home/postgres/.sql

创建用户以及授权

alter database ga_zj_taizhou owner to gazjtaizhou;

grant all privileges on database xc_hb_qinhuang to xchbqinhuan
g;

授权

将数据库的权限，授权给特定的用户。

#创建授权的用户
postgres=# create user test01_user with password 'Test01@123';

postgres=# create database test01 owner test01_user;

#授权数据库
postgres=#grant all privileges on database test01 to test01_user;
postgres=#grant select,insert,update on database test01 to test01_user;

#授权表
/* 赋给用户表的所有权限 */
GRANT ALL ON tablename TO user; 
/* 撤销用户权限 */
REVOKE privileges ON tablename FROM user;

或者
给用户 "runoob" 分配权限：
# GRANT ALL ON COMPANY TO runoob;
GRANT
信息 GRANT 表示所有权限已经分配给了 "runoob"。

runoobdb=# REVOKE ALL ON COMPANY FROM runoob;
REVOKE
信息 REVOKE 表示已经将用户的权限撤销。

删除用户：
runoobdb=# DROP USER runoob;
DROP ROLE
信息 DROP ROLE 表示用户 "runoob" 已经从数据库中删除。

授权

postgres@shhkfys$for i in `cat c `;do psql  ga_zj_taizhou -c "grant all on $i    to gazjtaizhou;" ;done

postgres@shhkfys:~$psql  ga_zj_taizhou            #进入ga_zj_taizhou数据库，查看数据库的所属者是不是gazjtaizhou，以及所有授权

postgres@shhkfys:~$ for i in `cat c `;do psql  ga_zj_taizhou -c "alter table $i owner to gazjtaizhou;" ;done                  #更改ga_zj_taizhou数据库里面的表的所有者为gazjtaizhou
postgres@shhkfys:~$psql  ga_zj_taizhou                   #查看ga_zj_taizhou数据库表的所属主。

测试连接

# psql -h 192.168.1.106 -p 5432 -U test01_user  

# psql -h 192.168.1.106 -p 5432 -U test01_user -d test01

# psql -h 192.168.1.106 -p 5432 -U test02_user -d test02

# psql -h 192.168.1.106 -p 5432 -U postgres -d  test01

# psql -h 192.168.1.106 -p 5432 -U postgres -d    test02

可以看出，postgres用某个用户登录，默认登录同名数据库，如果用户名和数据库不同，需要特别指定，否则会报找不到库！

备份所有的数据库

PostgreSQL备份
如果原系统中有PostgreSQL数据库，需要备份数据库。

chroot /mnt
su - postgres
pg_dumpall >database-20180201.sql

postgres@shhkfys:~$sudo pg_dump xc_gj_rainbow > ga-zj-taizhou.2020330.sql    #将ga-zj-taizhou.2020330.sql导入到xc_gj_rainbow数据库中

扩展

MySQL创建用户并授权db权限

mysql> create database mydb;
mysql> create user a_user identified by 'ThePassword'
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> grant all privileges on mydb.* to a_user@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Postgres创建用户并授权db权限
对于Postgres9.0以上

创建一个用户etl_user，允许登陆，永不过期

create role etl_user login password 'ThePassowrd' valid until 'infinity';

将db权限授予user

grant all privileges on database mydb to etl_user ;

或者，分别授权

GRANT CONNECT ON DATABASE mydb TO etl_user;
GRANT USAGE ON SCHEMA public TO etl_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO etl_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO etl_user;

这时候，如果你要删除这个role，则必须先要删除关联的privileges

REVOKE CONNECT ON DATABASE mydb FROM etl_user;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM etl_user;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM etl_user;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM etl_user;
DROP USER etl_user;

逻辑备份恢复主要以下三种：

pg_dump

pg_dumpall

copy

常见报错

1、新建数据库失败。

解决办法 ：

数据库里面不识别-符号

下面是正确写法

#pg_dump xc_gj_rainbow |psql ga_zj_taizhou

2、授权之后，链接数据库失败

执行命令

postgres=# grant all privileges on database ga_zj_taizhou to gazjtaizhou;

报错信息 ： ERROR : permission denied for relation permission

解决办法 ：

进入需要授权的数据库里面

postgres=# \c ga_zj_taizhou      #进入数据库里面
You are now connected to database "ga_zj_taizhou" as user "postgres".

#更改该数据库的属主
ga_zj_taizhou=# alter database ga_zj_taizhou owner to gazjtaizhou;    
ALTER DATABASE

#将该数据库里面的所有表都授权给该用户
ga_zj_taizhou=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO gazjtaizhou;                 
GRANT

#在该数据库里面，再次将该数据库的权限授权给该用户
ga_zj_taizhou=# grant all privileges on database ga_zj_taizhou to gazjtaizhou;        
GRANT
ga_zj_taizhou=# flush;      #刷新

3、序列没有授权

报错信息 ：

解决办法：

对序列单独授权

对postgres数据库里面的序列全部授权

4、删除数据库报错

报错信息 ：

postgres=# drop database ga_zj_taizhou;
ERROR:  database "ga_zj_taizhou" is being accessed by other users
DETAIL:  There is 1 other session using the database.

这个意思是说，删除数据库失败，因为这里还有3个链接连接到该数据库上，PostgreSQL在有进程连接到数据库时，对应的数据库是不运行被删除的。

那么怎么办呢？
解决方式：断开连接到这个数据库上的所有链接，再删除数据库。怎么断开呢？在PostgreSQL 9.2 及以上版本，执行下
面的语句：
解决办法 ：

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='testdb' AND pid<>pg_backend_pid();

执行上面的语句之后，在执行DROP操作，就可以删除数据库了。

上面语句说明：
pg_terminate_backend：用来终止与数据库的连接的进程id的函数。
pg_stat_activity：是一个系统表，用于存储服务进程的属性和状态。
pg_backend_pid()：是一个系统函数，获取附加到当前会话的服务器进程的ID

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='ga_zj_taizhou' AND pid<>pg_backend_pid();

删除成功

5、导入数据库数据失败

数据库存在，报错数据库不存在。

postgres@shhkfys:~$ pg_dump xc-gj-wxb > xc-gj-wxb.sql
pg_dump: [archiver (db)] connection to database "xc-gj-wxb" failed: FATAL:  database "xc-gj-wxb" does not exist
postgres@shhkfys:~$ psql -h localhost -p 3500 -U xcgjwxb
Password for user xcgjwxb: 
psql.bin: FATAL:  database "xcgjwxb" does not exist

psql进入数据库，使用\l+命令查看数据库，发现数据库存在，

复制数据库，报错数据库不存在。

postgres@shhkfys:/root$ pg_dump xc-gj-wxb |psql xc-hb-qinhuang  
could not change directory to "/root": Permission denied
pg_dump: [archiver (db)] connection to database "xc-gj-wxb" failed: FATAL:  database "xc-gj-wxb" does not exist
could not change directory to "/root": Permission denied
psql.bin: FATAL:  database "xc-hb-qinhuang" does not exist

postgres@shhkfys:~$ pg_dump xc-gj-wxb |psql xc-hb-qinhuang  
pg_dump: [archiver (db)] connection to database "xc-gj-wxb" failed: FATAL:  database "xc-gj-wxb" does not exist
psql.bin: FATAL:  database "xc-hb-qinhuang" does not exist

查看发现是数据库存在的

解决办法：

显式添加“ -h localhost”，这将修复它

psql: FATAL: role “postgres” does not exist 解决方案

在postgres的虚拟机里（而不是terminal）

CREATE USER postgres SUPERUSER;

参考链接 ：
PostgreSQL之pgdump备份恢复 ：

【PostgreSQL】PostgreSQL创建数据库、用户、授权 ：
https://www.jianshu.com/p/70bfffd6fa2c

postgresql数据库删除时提示回话 sessions using the database

postges 和MySQL创建用户并授权db权限 :

postgres复制数据库 ：

pg_dump备份失败，错误信息提示pg_dump: [archiver (db)] query failed: ERROR: schema “pgs_distribution_metadata” does not exist : https://developer.aliyun.com/ask/67512?spm=a2c6h.13159736
扩展
PostgreSQL 设置允许访问IP

postgresql数据库用户名密码验证失败

PostgreSQL的访问控制（pg_hba.conf）

Postgresql 远程连接配置

PostgreSQL远程连接配置管理/账号密码分配

https://yq.aliyun.com/articles/599287

Postgres password authentication fails

https://stackoverflow.com/questions/14564644/postgres-password-authentication-fails?rq=1

https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge/26735105#26735105