pgsql常用操作

docker run -e TZ="Asia/Shanghai" -d -t -i --name pgsql -p 5432:5432 --restart=always -e POSTGRES_PASSWORD=123456 -v /home/docker/data/pgsql:/var/lib/postgresql/data postgres:9.5.18

 

pgsql备份：

--进入pgsql容器
docker exec -it 容器ID bash

--备份指定数据库
/opt/rh/rh-postgresql95/root/usr/bin/pg_dump -h localhost -U postgres eibd_odc_test > /opt/eibd_odc_test.bak

--备份指定模式数据
pg_dump -h localhost -U postgres -d 库名 -n 模式名 > /opt/test.bak

--备份全库
pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -c -f db_bak.sql

--恢复全库
psql -h 127.0.0.1 -p 5432 -U postgres -f db_bak.sql

--备份全库排除部分表(--exclude-table-data=表名: 只备份表结构不备份表数据)
--如果模式不是public,则需添加模式名：--exclude-table-data=schema.table_name
pg_dump -h localhost -U postgres -d 库名 -n public --exclude-table-data=表名 > /opt/库名.bak

--拷贝文件至宿主机
docker cp 容器ID:/文件路径 .

--拷贝文件至容器
docker cp 文件路径 容器ID:/存放路径

--10.110.63.13恢复pgsql
/opt/rh/rh-postgresql95/root/usr/bin/psql -h localhost -U postgres eibd_odc_test < /opt/eibd_odc_test.bak

--查看持久化下的每个库对应的oid和库名
select oid,datname from pg_database;

 

pgsql导出多张表：

docker exec 容器ID pg_dump -U 用户名 库名 -t 表名 -t 表名 ... > odc_bak_sql.sql

pgsql导入多张表：

docker exec 容器ID pg_dump -U 用户名 库名 -d < odc_bak_sql.sql

pgsl清空表数据及级联，不删除结构

TRUNCATE TABLE 表名 CASCADE;

--创建用户：
create user eibd_odc_user with PASSWORD 'eibd_odc_user,.';

--指定权限：
GRANT ALL PRIVILEGES ON DATABASE eibd_odc to eibd_odc_user;

--根据模式给用户指定权限：
GRANT SELECT ON ALL TABLES IN SCHEMA public to dev,test;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to dev,test;
--开启日志记录： alter system set logging_collector='on'; --然后重启pgsql服务show logging_collector;查询状态是否为on
--给所有序列赋权
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA 模式名 to 用户名;

--给模式赋权
GRANT ALL ON schema 模式名 TO 用户名;
--不允许任何角色在public模式中创建对象
REVOKE ALL ON schema public FROM public;

--将所有表的所有者赋予给普通用户：
#查询所有表
select * from information_schema.tables where table_schema='public';
#拼接sql(执行生成下来的sql)
select 'ALTER TABLE ' || table_name || ' OWNER TO yourowner;' from information_schema.tables where table_schema='public';
--将所有序列的所有者赋予给普通用户;
select 'ALTER SEQUENCE ' || relname || ' OWNER TO yourowner;' from pg_class where relkind='S';
--查看数据库中有哪些序列--r =普通表， i =索引，S =序列，v =视图，m =物化视图， c =复合类型，t = TOAST表，f =外部表
select * from pg_class where relkind='S'

1.创建一个用户名为readonly密码为ropass的用户
CREATE USER readonly WITH ENCRYPTED PASSWORD 'ropass'；

2.用户只读事务
alter user readonly set default_transaction_read_only=on;

3.把所有库的语言的USAGE权限给到readonly
GRANT USAGE ON SCHEMA public to readonly;

4.授予select权限(这句要进入具体数据库操作在哪个db环境执行就授予那个db的权)
grant select on all tables in schema public to readonly;

5.创建数据库并设置字符集
create database db TEMPLATE template0 ENCODING 'UTF8' ;
CREATE DATABASE "database" WITH OWNER = postgres ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' TABLESPACE = pg_default CONNECTION LIMIT = -1;

6.删除正在使用中的数据库
#设置禁止连接
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'db_name';
#中断当前库中所有连接会话
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db_name';
#删库
drop database db_name;

 

pgsql之连接数修改

--查看当前在使用的连接数
select count(1) from pg_stat_activity;

--显示最大连接数
show max_connections;

--显示系统保留用户数
show superuser_reserved_connections;

--按照用户分组查看
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc;

--修改最大连接数(修改完成后需要重启pgsql服务)
alter system set max_connections=数量

docker run -e TZ="Asia/Shanghai" -d -t -i --name pgsql -p 5432:5432 --restart=always -e POSTGRES_PASSWORD=postgres,.1q -v /home/docker/data/pgsql:/var/lib/postgresql/data postgres:9.5.18

 

pgsql设置免密码连接

设置所有主机执行数据库命令不需要输入密码：

1 修改配置文件：/var/lib/pgsql/data/pg_hba.conf

将里面第一条设置为：

host    all             all             all               trust

2 重启数据库：systemctl restart postgresql


解释：

将host(远程连接)/local(本地连接)设置成md5(需要验证密码)或trust(不需要验证密码)

3 修改postgres密码

ALTER USER postgres WITH PASSWORD 'postgres';

pgsql开启日志记录功能(/var/lib/postgresql/data/postgres.conf)

log_statement = 'all'

# This is used when logging to stderr:
logging_collector = on        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'log'            # directory where log files are written,
                    # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
log_file_mode = 0600            # creation mode for log files,
                    # begin with 0 to use octal notation

查看当前库sehcma大小,并按schema大小排序

SELECT schema_name, 
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    round((sum(table_size) / pg_database_size(current_database())) * 100,2)
        as "percent(%)"
FROM (
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;

查询各个库大小

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database; 

查看表大小

select pg_size_pretty(pg_total_relation_size('test'));

--查出所有表（包含索引）并排序
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 limit 20

--查出表大小按大小排序并分离data与index
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes

查看base下的OID对应的库

select oid, datname from pg_database;

查询schema下所有表行数

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;

常用SQL

# 在mobile_phone字段的值后追加数字6(条件：不为空)
update ck_user set mobile_phone = mobile_phone||'6' where mobile_phone is not null;

常用命令

# 启动（-D 数据存放路径）
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
# 停止
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop

 

更多参考链接：https://www.it1352.com/1761312.html