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
--拷贝文件至宿主机 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 用户名;
--将所有表的所有者赋予给普通用户：
#查询所有表
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';
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' ;
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;