Install

  • Ubuntu
# client
sudo apt-get install postgresql-client

# server
sudo apt-get install postgresql
  • CentOS
wget --no-check-certificate https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-libs-10.19-1PGDG.rhel7.x86_64.rpm
wget --no-check-certificate https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-10.19-1PGDG.rhel7.x86_64.rpm
wget --no-check-certificate https://yum.postgresql.org/10/redhat/rhel-7-x86_64/postgresql10-server-10.19-1PGDG.rhel7.x86_64.rpm

sudo yum install postgresql10-libs-10.19-1PGDG.rhel7.x86_64.rpm
sudo yum install postgresql10-10.19-1PGDG.rhel7.x86_64.rpm
sudo yum install postgresql10-server-10.19-1PGDG.rhel7.x86_64.rpm

sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
sudo systemctl start postgresql-10
sudo systemctl status postgresql-10

这时必须要切换到postgres用户sudo su - postgres才能链接，需要修改配置文件

sudo vi /var/lib/pgsql/10/data/pg_hba.conf

修改为

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

然后可以通过以下命令从客户端链接

psql -h 127.0.0.1 -p 5432 -U postgres -d exampledb

远程连接

  1. 为用户添加密码
ALTER USER postgres PASSWORD 'password'

// 或创建新用户
CREATE USER dbuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
  1. 修改配置/var/lib/pgsql/10/data/pg_hba.conf为密码登录
# 本地登录无需密码
host    all             all             127.0.0.1/32            trust
# 远程登录需要密码
host    all             all             0.0.0.0/0            	password
  1. 修改配置文件/var/lib/pgsql/10/data/postgresql.conf，运行远程连接
listen_addresses = '*'
  1. 重启服务
sudo systemctl restart postgresql-10

Docker

导出配置文件

docker run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample > my-postgres.conf

修改配置文件 postgres.conf，添加网络、时区、SSL 等配置

listen_addresses = '*'
port = 5432

timezone = 'Asia/Shanghai'

ssl = on
ssl_ca_file = '/etc/ssl/postgres/ca.pem'
ssl_cert_file = '/etc/ssl/postgres/cert.pem'
ssl_key_file = '/etc/ssl/postgres/cert.pem'

配置权限文件 ``pg_hba.conf`

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# SSL
hostssl	all		all		0.0.0.0/0		md5
#host	all		all 	0.0.0.0/0 		md5

启动

docker run -d --name postgres --restart unless-stopped \
    -p 5432:5432 \
    -v /data/postgres:/var/lib/postgresql/data \
    -v $(pwd)/postgres.conf:/etc/postgres/postgres.conf \
    -v $(pwd)/pg_hba.conf:/etc/postgres/pg_hba.conf \
    -v /home/your_username/.ssl:/etc/ssl/postgres \
    -e POSTGRES_USER='your_username' \
    -e POSTGRES_PASSWORD='your_password' \
    postgres \
    -c 'config_file=/etc/postgres/postgres.conf' \
    -c 'hba_file=/etc/postgres/pg_hba.conf'

测试本地链接

docker exec -it postgres psql "host=localhost port=5432 user=your_username dbname=postgres"

测试远程连接

docker run -it --rm \
    -v /home/yzchen/.ssl:/etc/ssl/postgres \
    postgres psql "host=your_hostname port=5432 user=your_username dbname=postgres sslmode=verify-full sslrootcert=/etc/ssl/postgres/ca.pem sslcert=/etc/ssl/postgres/cert.pem sslkey=/etc/ssl/postgres/cert.pem"

参考

控制台命令

  • \h：查看SQL命令的解释，比如\h select
  • \?：查看psql命令列表
  • \l：列出所有数据库
  • \c [database_name]：连接其他数据库
  • \d：列出当前数据库的所有表格
  • \d [table_name]：列出某一张表格的结构
  • \du：列出所有用户
  • \e：打开文本编辑器
  • \conninfo：列出当前数据库和连接
  • \x on：按列显示

Dump and Restore

# dump
pg_dump -h 127.0.0.1 -p 5432 -U your_user_name database_name > database_name_YYYYMMDD.sql

# dump table data as insert SQL
pg_dump -h 127.0.0.1 -p 5432 -U your_user_name database_name --table=source_table --data-only --column-inserts > tmp.sql

# restore
psql template1 -c 'drop database database_name;' # 先删掉原数据库
psql template1 -c 'create database database_name with owner your_user_name;'
psql database_name -h 127.0.0.1 -p 5432 -U your_user_name < database_name_YYYYMMDD.sql

“template1” 和 “template0” 是 pgsql 的模板数据库。创建新database时，PostgreSQL会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据文件。

导出 CSV

COPY (SELECT column1, column2, ... FROM table_name WHERE condition) TO '/path/to/output/file.csv' DELIMITER ',' CSV HEADER;

导出JSON

\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;

SQL

# create database
CREATE DATABASE test;

# create table
CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# insert and ignore conflict
INSERT INTO my_table (filed1, filed2) VALUES (123, 'abc')
ON CONFLICT DO NOTHING;

# upsert
INSERT INTO my_table (filed1, filed2) VALUES (123, 'abc')
ON CONFLICT (filed1, filed2) DO UPDATE SET filed3 = 'update';

Time

# timestamp
SELECT NOW();

# timestamp to string
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

# string to timestamp
SELECT TO_TIMESTAMP('2022-06-21 20:37:51', 'YYYY-MM-DD HH24:MI:SS');

# timestamp to epoch
SELECT CAST(EXTRACT(EPOCH FROM NOW()) AS INTEGER);

# epoch to timestamp
SELECT TO_TIMESTAMP(1656669813);

Cast

# string to int
SELECT CAST(id AS INT) FROM users;

# string array to int array
SELECT str_arr::int[] FROM users;

String

# string to array
SELECT STRING_TO_ARRAY(name_path, ',') FROM groups;
SELECT STRING_TO_ARRAY(id_path, ',')::int[] FROM groups;

NULL

# check a column is null
SELECT * FROM accounts WHERE itime IS NULL;

Python client

Install

pip3 install records
pip3 install psycopg2-binary

Usage

post_url = f"postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

# query
QUERY_SELECT = '''
    SELECT name, age FROM my_table;
'''

with records.Database(mysql_url) as db:
    db.query(QUERY_SELECT)
    
# insert
QUERY_INSERT = '''
    INSERT INTO my_table (name, age) VALUES (:name, :age);
'''

with records.Database(mysql_url) as db:
    db.query(QUERY_INSERT, "Tom", 12)

References