Postgresql 常用操作

# 常用操作

```sql
-- 创建用户
CREATE USER odoo WITH PASSWORD 'odoo';

-- 修改用户密码
ALTER USER odoo WITH PASSWORD 'odoo';

-- 以用户的身份创建数据库
CREATE DATABASE odoo OWNER odoo;

-- 如果创建时未设置 owner
alter database exampledb owner to dbuser;

-- 将数据库的所有权限授予用户
GRANT ALL PRIVILEGES ON DATABASE odoo TO odoo;

-- 授予用户创建数据库权限
ALTER USER odoo CREATEDB;
```

# postgresql 不重启的情况下重新加载 `pg_hba.conf` 文件

<https://pgpedia.info/p/pg_reload_conf.html>

```sql
postgres=# SELECT pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
```

# 数据库恢复及数据处理

## 彻底卸载

```
apt-get --purge remove postgresql\*
rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres
```

## 安装
默认情况下，所有 Ubuntu 版本都提供 PostgreSQL。但是，Ubuntu“快照”了一个特定版本的 PostgreSQL，然后在该 Ubuntu 版本的整个生命周期中都支持该版本。其他版本的 PostgreSQL 可通过 PostgreSQL apt 存储库获得。
如果您的 Ubuntu 版本中包含的版本不是您想要的版本，您可以使用 PostgreSQL Apt Repository 。此存储库将与您的常规系统和补丁管理集成，并在 PostgreSQL 的整个支持生命周期内为所有受支持的 PostgreSQL 版本提供自动更新。
```
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql-14
```

## 配置数据库存储所在目录或位置

```
sudo /etc/init.d/postgresql stop  # 停止服务
sudo rsync -av /var/lib/postgresql /data/ # 复制权限及文件至目标目录
sudo vim /etc/postgresql/14/main/postgresql.conf
    data_directory = '/data/postgresql/14/main' # 修改为目标目录
sudo /etc/init.d/postgresql restart # 重启服务
```

查看配置文件位置

```
show config_file;        -- 查看配置文件位置
show data_directory;     -- 查看目录
show all;                -- 查看所有配置
```

## 删除数据库

```
drop database dodo9;
```

## 导出数据库数据

```
pg_dump -U postgres -f db.sql dbname

pg_dump -U odoo -d RCSP -f rcsp_dump_202004211337.sql -h 127.0.0.1
```

## 使用 psql 恢复 SQL 文本格式的数据备份：

将 SQL 文本中的数据恢复到某个指定的 database：

```
createdb newDBname
pg_restore -d dodo1028 dodo20191027160410.sql
```

```
psql -U postgres -d mydb -f db.sql
```

```
-- 修改所有邮箱
update hr_employee set work_email = concat(work_email,'-');

-- 将所有钉钉 id 设为空
update res_users set dd_userid=null;

-- 将计划任务时间改为 10 年后
update ir_cron set nextcall='2029-10-22 09:32:31';

update dtdream_contract_special_configuration set erp_url='https://eiey-test.fa.us2.oraclecloud.com:443'
update dtdream_erp_config set url='https://eiey-test.fa.us2.oraclecloud.com:443'
update dtdream_tool_erp_config  set root_url = 'https://eiey-test.fa.us2.oraclecloud.com:443';
update dtdream_journal_config set "rootUrl" = 'https://eiey-test.fa.us2.oraclecloud.com:443';

update dtdream_rpc_config set url = concat(url,'z');
```

```
# 技术 -> rpc 配置
# 财务分摊 -> erp 设置
# 订单 -> 统计性凭证 -> 凭证配置
```

# 数据库维护

```
-- 查看单个数据库占用的硬盘大小
select pg_size_pretty(pg_database_size('dodo9'));

-- 查看所有数据库占用的硬盘大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;

-- 查看所有表大小
SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

-- 查看表占用的空间
select pg_size_pretty(pg_table_size('dtdream_access_log'))

-- 查看索引占用的空间
select pg_size_pretty(pg_indexes_size('dtdream_access_log'))

-- 查看表 + 索引占用的空间
select pg_size_pretty(pg_total_relation_size('dtdream_access_log'))

-- 查看表对应的索引名称
select * from pg_indexes where tablename='dtdream_access_log';

-- 重建索引
REINDEX index dtdream_access_log_pkey

-- 真空表(释放空间)
Vacuum public.dtdream_access_log
```

# 锁

```sql
--查看当前活动的客户端连接数
 SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();

--查看 PostgreSQL 正在执行的 SQL   
SELECT   
    procpid,   
    start,   
    now() - start AS lap,   
    current_query   
FROM   
    (SELECT   
        backendid,   
        pg_stat_get_backend_pid(S.backendid) AS procpid,   
        pg_stat_get_backend_activity_start(S.backendid) AS start,   
       pg_stat_get_backend_activity(S.backendid) AS current_query   
    FROM   
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S   
    ) AS S ,pg_stat_activity pa  
WHERE   
   current_query <> '<IDLE>' and  procpid<> pg_backend_pid() and pa.pid=s.procpid and pa.state<>'idle'  
ORDER BY   
   lap DESC;

-- 停止正在执行的 sql  
SELECT pg_cancel_backend(进程 id);  
SELECT pg_terminate_backend(进程 id);

procpid：进程 id  
start：进程开始时间  
lap：经过时间  
current_query：执行中的 sql
```