PostgreSQL Tips

PostgreSQL-tips.md

mac OSX upgrade PostgreSQL

error message:

waiting for server to start....2020-10-22 09:27:22.125 CST [40884] FATAL:  database files are incompatible with server
2020-10-22 09:27:22.125 CST [40884] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.0.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

fix command:

brew postgresql-upgrade-database

start PostgreSQL:

pg_ctl -D /usr/local/var/postgres start

json

JSON Functions and Operators

将不带 timezone 的日期修改为带 timezone 的

ALTER TABLE a ALTER COLUMN t TYPE TIMESTAMP WITH TIME ZONE;

https://stackoverflow.com/questions/9772825/postgresql-alter-type-timestamp-without-time-zone-with-time-zone It is better to specify the time zone explicitly. Say, if your timestamp are supposed to be in UTC (but without timezone), you should be wary of the fact that the timezone of the client or server might be messing everything here. Instead write:

  ALTER TABLE a ALTER COLUMN t TYPE TIMESTAMP WITH TIME ZONE USING t AT TIME ZONE 'UTC';

alembic 中完成该操作

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from sqlalchemy import engine_from_config
from sqlalchemy.engine import reflection


def _table_has_column(inspector, table, column):
    has_column = False
    for col in inspector.get_columns(table):
        if column not in col['name']:
            continue
        has_column = True
    return has_column

def upgrade():
    config = op.get_context().config
    engine = engine_from_config(config.get_section(config.config_ini_section), prefix='sqlalchemy.')
    inspector = reflection.Inspector.from_engine(engine)
    tables = {'orders', }
    fields = {
        'created_at', 'updated_at',
    }
    for table in tables:
        for column in fields:
            if _table_has_column(inspector, table, column):
                print(f'xxxxx {table} HAS {column}')
                op.alter_column(
                    table_name=table,
                    column_name=column,
                    nullable=True,
                    type_=sa.types.DateTime(timezone=True)
                )

list all views

select table_schema as schema_name, table_name as view_name
    from information_schema.views
    where table_schema not in ('information_schema', 'pg_catalog')
    order by schema_name, view_name;

list all view in special table

select table_schema as schema_name, table_name as view_name
    from information_schema.views
    where table_schema in ('SPECIAL-TABLE-NAME')
    order by schema_name, view_name;

query which holds the lock in postgresql

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

performance

refs:

• Postgres 日志监控：阻塞，死锁，Checkpoint 优化（译）
• Postgres Failures At Scale

locks

lock_timeout

• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

statement_timeout

• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

SET lock_timeout TO '1000ms';
SET statement_timeout TO '1500ms';

set statement_timeout for role:

alter role <role-name> set statement_timeout = 2000;

https://github.com/zhangwm404/q2a/blob/master/Database/PostgreSQL-tips.md ，git repo 中搜索、管理更方便