postgres复制数据库 |
您所在的位置:网站首页 › 数据库流复制 › postgres复制数据库 |
介绍
pg_dump备份 只能备份单个数据库,不会导出角色和表空间相关的信息 -F c 备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原-F p 备份为文本,大库不推荐 更多介绍 :https://blog.csdn.net/pg_hgdb/article/details/79168060?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase 复制数据库新建abc数据库 [postgres@localhost ~]$ psql -c"create database abc" ----创建一个空库 CREATE DATABASE
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 copyhttps://blog.csdn.net/pg_hgdb/article/details/79168060?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase 常见报错 1、新建数据库失败。
执行命令 postgres=# grant all privileges on database ga_zj_taizhou to gazjtaizhou;报错信息 : ERROR : permission denied for relation permission 报错信息 : 报错信息 : 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.
那么怎么办呢? 解决方式:断开连接到这个数据库上的所有链接,再删除数据库。怎么断开呢?在PostgreSQL 9.2 及以上版本,执行下 面的语句: 解决办法 : SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='testdb' AND pidpg_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 pidpg_backend_pid();
数据库存在,报错数据库不存在。 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
在postgres的虚拟机里(而不是terminal) CREATE USER postgres SUPERUSER;参考链接 : PostgreSQL之pgdump备份恢复 :https://blog.csdn.net/pg_hgdb/article/details/79168060?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.nonecase 【PostgreSQL】PostgreSQL创建数据库、用户、授权 : https://www.jianshu.com/p/70bfffd6fa2c postgresql数据库删除时提示回话 sessions using the database https://blog.csdn.net/u010321349/article/details/88713058 postges 和MySQL创建用户并授权db权限 : https://www.cnblogs.com/woshimrf/p/postgres-grant-user-to-db.html postgres复制数据库 : https://blog.csdn.net/a3470194/article/details/16861651 https://www.cnblogs.com/yungiu/p/10983792.html 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 https://blog.csdn.net/wlchn/article/details/78915813 postgresql数据库用户名密码验证失败 https://blog.csdn.net/pg_hgdb/article/details/78805463 PostgreSQL的访问控制(pg_hba.conf) https://my.oschina.net/liuyuanyuangogo/blog/497239 Postgresql 远程连接配置 https://www.cnblogs.com/3Tai/p/4935303.html 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 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |