PostgreSQL常用配置

Tags:
Categories:

PostgreSQL常用配置

元命令

命令

    -- 修改表Owner
    alter table table_name owner to user_name;

# 命令行连接PostgreSQL
psql --host=192.168.4.20 --username=airflow --dbname=wavepoint --port=55332

# 命令行连接容器里的PostgreSQL
docker exec -it postgresql01 psql --host=localhost --username=airflow --dbname=wavepoint

配置

配置psql免密登陆

设置环境变量: PGPASSWORD

export PGPASSWORD=123123;psql -h localhost -p 5432 postgres postgres;

添加配置文件:

vim ~/.pgpass
# 添加如下内容
192.168.4.20:55332:wavepoint:airflow:airflow
192.168.4.135:55332:wavepoint:airflow:airflow
192.168.15.27:55332:wavepoint:airflow:airflow
localhost:5432:wedo:wedo:sdfsdf

# 修改权限
chmod 600 ~/.pgpass

格式: host:port:username:password

最大连接数配置

配置远程登陆

  1. 修改postgresql.conf
vim /var/lib/pgsql/10/data/postgresql.conf

listen_addresses='*'
  1. 修改pg_hba.conf 
    vim /var/lib/pgsql/10/data/pg_hba.conf

    host    all            all      127.0.0.1/32      md5
    host    all            all      all（需要连接的服务器IP,我设置为所有ip）  md5

逻辑复制槽

-- 查看当前slot信息
select * from pg_replication_slots;

-- 创建slot
select pg_create_logical_replication_slot('clickhouse_sync_1','pgoutput');

-- 删除slot
select pg_drop_replication_slot('clickhouse_sync_1');

--3.创建 snapshot （pg操作）
BEGIN;
select pg_export_snapshot();

--4.启用物化引擎 （ch client命令进去执行）
set allow_experimental_database_materialized_postgresql=1;

--5.创建物化库
drop database if exists dim;
create database dim engine = MaterializedPostgreSQL('pg.wavepoint:5432','wavepoint','airflow','airflow') settings materialized_postgresql_replication_slot  = 'clickhouse_sync_1',materialized_postgresql_snapshot = '00000054-00002E73-1';