PostgreSQL操作随记:修订间差异

来自三线的随记
无编辑摘要
无编辑摘要
第17行: 第17行:


* 五花八门的传递password值的方法: [https://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump how-to-pass-in-password-to-pg-dump]
* 五花八门的传递password值的方法: [https://stackoverflow.com/questions/2893954/how-to-pass-in-password-to-pg-dump how-to-pass-in-password-to-pg-dump]
* pg_restore 需要关注的 [https://dba.stackexchange.com/questions/82161/why-pg-restore-ignores-create-error-failed-fatal-database-new-db-does-n why-pg-restore-ignores-create-error-failed-fatal-database-new-db-does-n]
* pg_restore 需要关注的 [https://dba.stackexchange.com/questions/82161/why-pg-restore-ignores-create-error-failed-fatal-database-new-db-does-n why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist]


===连接数相关===
===连接数相关===

2022年7月28日 (四) 14:28的版本

PostgreSQL默认用户: postgres

Connect

psql -h host -U user -W

backup tools

pg_dump
PGPASSWORD="12345" pg_dump
pg_dumpall

需要注意导出的文件格式(using pg_dump -Fc generates the most flexible export format and is already compressed)

ps:

连接数相关

配置文件

sed  "s/max_connections = .*/max_connections = 1024/" /var/lib/postgresql/data/postgresql.conf|grep max_connection
sed -i "s/max_connections = .*/max_connections = 1024/" /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

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

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.