PostgreSQL操作随记

PostgreSQL默认用户: postgres

Connect

psql -h host -U user -W

backup tools

pg_dump

PGPASSWORD="12345" pg_dump

pg_dumpall

需要注意导出的文件格式,pg_dump不加format参数的话默认是plain text文件，即SQL语句，这种格式是不支持在pg_restore中使用的

(using pg_dump -Fc generates the most flexible export format and is already compressed)

example:

db=(db1 db2 db3)
for d in ${db[*]};do echo $d;set -x;pg_dump -U postgres -h pg_host -p pg_port -Fc ${d} -C -f ${d}.sql ;set +x;done;

pg_restore -h pg_host2 -U stolon -d test -C -O < db.sql

上述的例子pg_restore使用了-O参数以实现skip restoration of object ownership，以避免在还原数据库的时候出现ERROR:role "xxxx" does not exist错误，实际按需调整(-x, --no-privileges参数用于skip restoration of access privileges (grant/revoke))

另外还有额外参数-c，可以在pg_dump或者pg_restore的时候配合使用，用以在恢复时，覆盖原有数据

-c, --clean                  clean (drop) database objects before recreating

ps:

• 五花八门的传递password值的方法: how-to-pass-in-password-to-pg-dump
• pg_restore 需要关注的 why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist
  • The -d will restore in the given database if and only if -C is not used. If -C is used, the database is used as a "launchpad", not as the destination.

连接数相关

配置文件

sed  "s/max_connections = .*/max_connections = 400/" /var/lib/postgresql/data/postgresql.conf|grep max_connection

sed -i "s/max_connections = .*/max_connections = 400/" /var/lib/postgresql/data/postgresql.conf|grep max_connection

commands

临时修改连接数(待验证)

alter system set max_connections=1024

查看连接数

select count(1) from pg_stat_activity;

查看最大连接数值

show max_connections;

查看保留连接数

show superuser_reserved_connections;

查看连接的客户端信息

SELECT * FROM pg_stat_activity

select datname,pid,application_name,state from pg_stat_activity;

SELECT datname,count(datname)  FROM pg_stat_activity group by datname;

keywords: PSQLException: FATAL: sorry, too many clients already

stolon: 修改连接数配置

stolonctl --cluster-name=kube-stolon --store-backend=kubernetes --kube-resource-kind=configmap update --patch '{"pgParameters":{"max_connections":"300"}}'

也可以在stolon proxy中执行

stolonctl --cluster-name=${STPROXY_CLUSTER_NAME} --store-backend=${STPROXY_STORE_BACKEND} --kube-resource-kind=${STPROXY_KUBE_RESOURCE_KIND} update --patch '{"pgParameters":{"max_connections":"300"}}'

common commands

show databases in psql

SELECT datname FROM pg_database;

desc table in psql

postgres=# \c keel
You are now connected to database "keel" as user "postgres".
keel=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner
--------+----------------------+-------+----------
 public | app_permissions      | table | postgres
 public | app_versions         | table | postgres
 public | applications         | table | postgres
(30 rows)

keel=# \dt+
                              List of relations
 Schema |         Name         | Type  |  Owner   |    Size    | Description
--------+----------------------+-------+----------+------------+-------------
 public | app_permissions      | table | postgres | 8192 bytes |
 public | app_versions         | table | postgres | 72 kB      |
 public | applications         | table | postgres | 8192 bytes |
(30 rows)

enabling expanded display (like mysql \G)

# \x
Expanded display is on.
# \x
Expanded display is off.

run this for a single command by using the \x\g\x suffix to toggle expanded display on, run the query, then toggle it off again.

select * from foo \x\g\x

Note the lack of ; in the above line, this is intentional.