PostgreSQL常用命令
2020-11-03 本文已影响0人
酱油王0901
添加索引
demon=# CREATE INDEX IF NOT EXISTS "error_record_action_log_id_idx" ON "error_record" ("action_log_id");
CREATE INDEX
demon=# CREATE INDEX IF NOT EXISTS "error_record_alert_id_idx" ON "error_record" ("alert_id");
CREATE INDEX
demon=# \d error_record
Table "public.error_record"
Column | Type | Modifiers
---------------+--------------------------+-----------------------------------------------------------
id | integer | not null default nextval('error_record_id_seq'::regclass)
code | character varying(255) | not null default ''::character varying
message | text | not null default ''::text
details | text |
action_log_id | bigint |
alert_id | bigint |
host_id | bigint |
extra_data | text |
create | timestamp with time zone | not null
Indexes:
"error_record_pkey" PRIMARY KEY, btree (id)
"error_record_action_log_id_idx" btree (action_log_id)
"error_record_alert_id_idx" btree (alert_id)
- https://devcenter.heroku.com/articles/postgresql-indexes
- https://devcenter.heroku.com/articles/postgresql-concurrency
- https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan
修改
demon=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
id | name | volume_name | image_id | recycled | passive
----+----------+-------------+------------------------+----------+---------
84 | lun_v2_1 | lun_v2_1 | rbd_data.4f5f04a0178de | f | t
85 | lun_v2_2 | lun_v2_2 | rbd_data.4f5f0672ac672 | f | t
86 | lun_v2_3 | lun_v2_3 | rbd_data.4f5f07f04723a | f | t
(3 rows)
demon=# update volume set image_id=trim(leading 'rbd_data.' from volume.image_id) where image_id like '%rbd_data%';
UPDATE 3
demon=# select id, name, volume_name, image_id, recycled, passive from volume where image_id like '%rbd_data%';
id | name | volume_name | image_id | recycled | passive
----+------+-------------+----------+----------+---------
(0 rows)
demon=# select id, name, volume_name, image_id, recycled, passive from volume order by id desc;
id | name | volume_name | image_id | recycled | passive
----+----------+-------------+---------------+----------+---------
86 | lun_v2_3 | lun_v2_3 | 4f5f07f04723a | f | t
85 | lun_v2_2 | lun_v2_2 | 4f5f0672ac672 | f | t
84 | lun_v2_1 | lun_v2_1 | 4f5f04a0178de | f | t
36 | lun03 | lun03 | 18ac6c0f | f | t
(4 rows)
reset sequence
ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1;
alter sequence alert_sequence_id start 1;
UPDATE foo SET id = DEFAULT;
List table triggers
select event_object_schema as table_schema,
event_object_table as table_name,
trigger_schema,
trigger_name,
string_agg(event_manipulation, ',') as event,
action_timing as activation,
action_condition as condition,
action_statement as definition
from information_schema.triggers
group by 1,2,3,4,6,7,8
order by table_schema,
table_name;
List idle transactions
demon=# select * from pg_stat_activity where (state = 'idle in transaction') and xact_start is not null;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
-------+---------+-----+----------+------------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+---------------------+-------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------
16385 | demon | 50 | 16384 | demon_user | | 127.0.0.1 | | 33558 | 2020-10-13 02:20:02.043835+00 | 2020-10-13 03:19:35.695803+00 | 2020-10-13 03:19:35.697997+00 | 2020-10-13 03:19:35.701515+00 | | | idle in transaction | 2473 | | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9
(1 row)
demon=# SELECT
demon-# pid,
demon-# now() - pg_stat_activity.query_start AS duration,
demon-# query,
demon-# state
demon-# FROM pg_stat_activity
demon-# WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
pid | duration | query | state
-----+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------
50 | 00:23:27.889451 | UPDATE "host" SET "name" = $1, "vendor" = $2, "model" = $3, "cpu_model" = $4, "cores" = $5, "memory_kbyte" = $6, "os" = $7, "enclosures" = $8 WHERE "id" = $9 | idle in transaction
(1 row)
demon=# select pg_cancel_backend(50); pg_cancel_backend
-------------------
t
(1 row)
demon=# select pg_terminate_backend(50);
pg_terminate_backend
----------------------
t
(1 row)
check replication status
On master:
select * from pg_stat_replication;
demon=# select * from pg_stat_replication ;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+------------------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
3256 | 16386 | demon_replicator | walreceiver | 10.252.3.171 | | 48634 | 2020-10-27 03:13:57.134488+00 | | streaming | 0/28664B00 | 0/28664B00 | 0/28664B00 | 0/28664B00 | 0 | async
3276 | 16386 | demon_replicator | walreceiver | 10.252.3.172 | | 44150 | 2020-10-27 03:18:37.949817+00 | | streaming | 0/28664B00 | 0/28664B00 | 0/28664B00 | 0/28664B00 | 0 | async
(2 rows)
demon=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
On replica (streaming replication in my case):
select * from pg_stat_wal_receiver;
demon=# select * from pg_stat_wal_receiver ;
pid | status | receive_start_lsn | receive_start_tli | received_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | conninfo
-----+-----------+-------------------+-------------------+--------------+--------------+------------------------------+-------------------------------+----------------+------------------------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
44 | streaming | 0/6000000 | 1 | 0/286B4320 | 1 | 2020-11-02 11:32:49.16744+00 | 2020-11-02 11:32:16.011279+00 | 0/286B4320 | 2020-11-02 11:32:49.16744+00 | | user=demon_replicator password=******** dbname=replication host=10.252.3.170 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1
(1 row)
demon=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
Restore data from pg_dump
demon=# create database xsky;
CREATE DATABASE
(ENV) [root@ceph-2 ~]# docker exec -i -u postgres sds-postgres pg_restore -d xsky < ~/20201031115700
(ENV) [root@ceph-2 ~]# docker exec -it -u postgres sds-postgres psql xsky
psql (9.6.6)
Type "help" for help.
xsky=# select count(*) from volume;
count
-------
1402
(1 row)
References
- Finding and killing long running queries on PostgreSQL
- # 9.26. System Administration Functions
- In PostgreSQL, as in life, don’t wait too long to commit
- Monitoring Postgres Replication
- pgmetrics
- pgmetrics使用介绍
- How to Restore Database Dumps for Postgres in Docker Container
- Quick: How to dump & restore a PostgreSQL database from a docker container
