---
title: "Postgresql 操作"
date: 2022-10-12T15:06:02+07:00
draft: true
---

## 基本环境

- OS: Manjaro(Arch Serial)
- Postgresql: v14.5.1

## 手册

https://github.com/postgres-cn/pgdoc-cn

## 安装

1. arch linux 安裝
```shell
pacman -Syu
pacman -S postgresql
```

2. 運行
```shell
sudo su - postgres
initdb --locale en_US.UTF-8 -D /var/lib/postgres/data
exit # quit postgres user shell

sudo su - postgres
createdb <database>
createuser <username>
dropdb <database>
dropuser <username>
## 以上命令或者 `sudo -u postgres createdb`
```

```shell
sudo -u postgres psql # enter psql
alter user <username> with encrypted password 'password';
grant all privileges on database <database> to <username>;
```

```shell
sudo su - postgres
create user <username> with password 'password';
create database <database> owner <username>;
grant all privileges on database <database> to <username>;
\q
```

3. 命令
```shell
1. \h SQL help
2. \? psql help
3. \l show all databases;
4. \c <database> <username> connect to <database>
5. \d list all tables;
6. \du list all users
7. \e open editor
8. \conninfo: list database and connection info
9. \password: modify password
10. \z
11. \dp [tablename]: 查看对象的访问权限列表
```

## 常用 SQL

```sql
-- 查看当前用户
select user; 
-- 当前数据库
select current_database();
-- 建表
create table <schema>.<table>();
-- 角色
create role <role>;
alter role <role> with CREATEDB;
drop role <role>
-- 能登录的用户
select * from pg_user;
-- 所有角色
select * from pg_roles;
-- 查看 guest 表权限
select * from information_schema.role_table_grants where grantee='guest' group by table_name,table_schema;
-- 查看表所属权限
select * from information_schema.role_table_grants where table_name='tablename' group by grantee,table_schema;
-- 查看用户表权限
select * from information_schema.table_privileges where grantee='guest';
-- 查看用户的 USAGE 权限
select * from information_schema.usage_privileges where grantee='guest';
-- 检查权限
select has_database_privilege(<user>, <database>, <privilege>);
select has_schema_privilege(<user>, <schema>, <privilege>);
select has_table_privilege(<user>, <table>, <privilege>);
-- 查看 schema 所有表
\dt <schema>.*
select * from information_schema.tables where table_schema='public';

```
### 
#### 升级问题

滚动更新后会更新 Postgresql 到新版本，再次启动 `systemctl restart postgresql` 后可能会报错 `An old version of the database format was found.` 导致启动失败。解决如下

https://webhostinggeeks.com/howto/how-to-fix-an-old-version-of-the-database-format-was-found-while-starting-postgresql/

升级数据
https://suay.site/?p=1130

```shell

```

stream replica
https://girders.org/postgresql/2021/11/05/setup-postgresql14-replication/

logic replica
https://hevodata.com/learn/postgresql-master-slave-replication/#intro

Connection pool
https://github.com/yandex/odyssey
https://pgpool.net/mediawiki/index.php/Main_Page

Replication extension
https://github.com/2ndQuadrant/pglogical

Export Import
https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/importing-and-exporting-data-in-postgresql#data-export-with-pg_dump