PostgreSQL

# CentOS例子
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装client,server,extension
sudo yum install -y postgresql12-server postgresql12-contrib
# init and auto start
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

# Ubuntu
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
sudo ss -atnp | grep 5432
sudo -u postgres psql

# 删除老版本
sudo apt purge postgresql-9.5 postgresql-10 postgresql-12

# 默认安装PostgreSQL 10
sudo apt install postgresql
sudo passwd postgres
# 启动服务
sudo service postgresql start
# 连接
sudo -u postgres psql

# 默认localhost,如果需要远程访问修改为*
listen_addresses = '*'

# 默认配置
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# host    all             all             127.0.0.1/32            ident


# 开放远程访问
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all            192.168.100.1/24            md5

# 连接服务器
psql -h hostname database user
# 创建数据库
sudo -u postgres createuser xulz -s
sudo -u postgres createdb mydb
> createuser
     -E  --encrypted (store password)
     -s   --superuser
     -P  --pwprompt
> createdb
> dropdb -W -U postgres -h localhost your_db_name

-- 连接
sudo -u postgres psql
-- 创建用户
CREATE USER xulz WITH PASSWORD 'password';
-- 更改密码
ALTER USER "user_name" WITH PASSWORD 'new_password';
-- 修改postgres密码
alter user postgres password 'postgres';  #或者 \password postgres
    
-- 创建数据库
CREATE DATABASE grafana;    
    
-- 用户授权
-- GRANT ALL PRIVILEGES ON DATABASE 授予 CREATE, CONNECT和 TEMPORARY 权限
grant ALL PRIVILEGES on database grafana to xulz;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xulz;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO xulz;
-- pg14
grant pg_read_all_data to xulz;
grant pg_write_all_data to xulz;

-- 重命名数据库
ALTER DATABASE people RENAME TO "customers";

-- 设置密码
export PGPASSWORD=xxxxxxxx

# 清空表数据
TRUNCATE bigtable RESTART IDENTITY;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname'
AND pid <> pg_backend_pid();
-- 避免关闭当前连接
REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username; 
-- Revoke CONNECT权限以避免创建新连接

SELECT
    nspname AS schemaname,relname,reltuples
FROM pg_class C
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
        nspname NOT IN ('pg_catalog', 'information_schema') AND
        relkind='r'
ORDER BY reltuples DESC;

## 备份
pg_dump -U postgres grafana -f grafana_bak.sql -W -h localhost
pg_dump dbname -f db_bak.sql
# 或者
pg_dump -Fp dbname  > db_bak.sql


## psql命令还原
psql -U username -f db_bak.sql dbname 
# 或者直接在psql终端还原
postgres=# \i db_bak.sql

pg_dump -Fc dbname -f filename 
# 还原
pg_restore -Fc -d dbname filename

# tar格式与此类似，-Ft

-- —clean 清除数据库
pg_dump -U postgres -h localhost -p 5432 --clean --file=sandbox.sql sandbox
pg_dumpall -U postgres -h localhost -p 5432 --clean --globals-only --file=globals.sql
psql -W -U postgres -h localhost your_db_name < backup.sql
drop schema public cascade;
create schema public;

DO $$ DECLARE
r RECORD;
BEGIN
-- 注意替换current_schema为要删除的schema
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$