postgresql官网地址:https://www.postgresql.org/
postgresql中文社区:http://www.postgres.cn/
本篇博文主要记录如何源码安装postgresql 12.6 !
$ useradd postgres $ echo postgres | passwd --stdin postgres
$ yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility" $ yum install -y bison flex readline* zlib-devel gcc* gmake
# 内核优化 $ cat >> /etc/sysctl.conf << EOF kernel.shmmax = 68719476736 kernel.shmall = 4294967296 kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 EOF $ sysctl -p $ cat >> /etc/security/limits.conf << EOF * soft nofile 131072 * soft nproc 131072 * hard nproc 131072 * soft core unlimited * hard core unlimited * soft memlock 50000000 * hard memlock 50000000 EOF
$ mkdir -p /usr/local/pg12 $ mkdir -p /pgdata/12/data $ chown -R postgres. /pgdata $ chown -R postgres. /usr/local/pg12 $ chmod -R 700 /pgdata/12/data/
$ cd /opt $ wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.6/postgresql-12.6.tar.gz $ tar xf postgresql-12.6.tar.gz $ cd postgresql-12.6/ $ ./configure --prefix=/usr/local/pg12 --with-pgport=1921 $ gmake world $ gmake install-world
$ su - postgres [postgres@pg1 ~]$ vim .bash_profile export PGDATA=/pgdata/12/data export LANG=en_US.utf8 export PGHOME=/usr/local/pg12 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export DATE=`date +%Y%m%d%H%M` export PATH=$PGHOME/bin:$PATH export MANPATH=$PGHOME/share/man:$MANPATH export PGUSER=postgres [postgres@pg1 ~]$ source .bash_profile [postgres@pg1 ~]$ psql --version psql (PostgreSQL) 12.6
[postgres@pg1 ~]$ initdb -D /pgdata/12/data -W # 简易初始化 [postgres@pg1 ~]$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -W # 生产建议 [postgres@pg1 ~]$ pg_ctl start # 启动postgresql [postgres@pg1 ~]$ psql # 使用本地socket连接到postgresql Password for user postgres: psql (12.6) postgres=# create database lvzhenjiang; # 创建lvzhenjiang数据库 postgres=# \c lvzhenjiang; # 切换到lvzhenjiang数据库 lvzhenjiang=# create table t1 (id int); # 创建t1表 lvzhenjiang=# insert into t1 values(1); # 向t1表中插入数据 lvzhenjiang=# select * from t1; # 查看t1表中的数据 id ---- 1 (1 row) lvzhenjiang=# \l # 查看所有的数据库 List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+---------+-------+----------------------- lvzhenjiang | postgres | UTF8 | C | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) lvzhenjiang=# \d # 查看lvzhenjiang库中的表 List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row) lvzhenjiang=# \dt # 查看lvzhenjiang库中的表 List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row)
[postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data -l logfile start [postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -ms [postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -mf # 常用关闭方式 [postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data stop -mi [postgres@pg1 ~]$ pg_ctl restart -mf
注: 若想添加为系统服务,使用systemctl指令来控制服务的启停,可以参考官方文档。
默认安装完成后 只能允许本地socket连接!
[postgres@pg1 ~]$ psql # 本地socket连接方式
[postgres@pg1 ~]$ vim $PGDATA/pg_hba.conf host all all 192.168.99.0/24 md5 [postgres@pg1 ~]$ cp $PGDATA/postgresql.conf{,.bak} [postgres@pg1 ~]$ vim $PGDATA/postgresql.conf listen_addresses = '*' # *表示监听所有地址 port = 5432 # 监听端口 max_connections = 1000 # 指定最大连接数 tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 shared_buffers = 1GB # 设置共享内存缓冲区的内存量,建议值为系统总内存的25% temp_buffers = 80MB # 设置用于每个数据库会话中的临时缓冲区的最大内存量 max_files_per_process = 65535 # 设置允许每个服务器子进程同时打开的文件的最大数量。不能大于ulimt -n的数值 bgwriter_delay = 100ms bgwriter_lru_maxpages = 1000 bgwriter_flush_after = 0 synchronous_commit = off wal_level = replica archive_mode = on # 以下路径替换为实际备份路径 archive_command = 'test ! -f /pgdata/12/data/backup/incre/%f && cp %p /pgdata/12/data/backup/incre/%f' full_page_writes = on wal_buffers = -1 wal_writer_delay = 100ms wal_writer_flush_after = 256kB checkpoint_timeout = 30min max_wal_size = 5GB min_wal_size = 1GB log_destination = 'stderr' logging_collector = on log_directory = 'logs' log_filename = 'postgresql-%Y-%m-%d_%H.log' log_file_mode = 0640 log_rotation_age = 1d log_rotation_size = 100MB log_truncate_on_rotation = off log_min_messages = notice log_min_error_statement = notice log_min_duration_statement = 3s log_checkpoints = on log_connections = on log_error_verbosity = verbose log_line_prefix = '%m ' log_timezone = 'PRC' track_activities = on log_autovacuum_min_duration = 3s autovacuum_max_workers = 4 autovacuum_naptime = 45s autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.1 autovacuum_freeze_max_age = 1600000000 autovacuum_multixact_freeze_max_age = 1600000000 vacuum_freeze_table_age = 1500000000 vacuum_multixact_freeze_table_age = 1500000000 datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'en_US.utf8' lc_monetary = 'en_US.utf8' lc_numeric = 'en_US.utf8' lc_time = 'en_US.utf8' default_text_search_config = 'pg_catalog.english'
关于配置文件的优化,可以参考官方文档。
[postgres@pg1 ~]$ pg_ctl restart -mf # 重启生效 [postgres@pg1 ~]$ psql -d postgres -h 192.168.99.4 -p 1921 -U postgres Password for user postgres: postgres=# # 测试命令行远程登陆 # 自行下载测试pgadmin连接pg https://www.pgadmin.org/ # 也可以通过navicat工具进行连接测试
用来登陆数据库实例、管理数据库对象!
create user # 默认自带连接功能(常用) create role
CREATE USER test1 WITH PASSWORD 'test1'; CREATE ROLE test2 WITH LOGIN PASSWORD 'test2' VALID UNTIL '2021-06-30'; CREATE USER admin WITH SUPERUSER PASSWORD 'admin'; CREATE USER repl RELICATION LOGIN ENCRYPTED PASSWORD 'repl'; DROP USER test1; # 删除用户 ALTER USER admin with PASSWORD 'admin123'; # 更改用户密码 ALTER USER admin WITH nologin PASSWORD 'admin123'; # 更改用户权限 \help CREATE USER; # 查看帮助 \du # 查看所有的用户
- cluster权限:实例权限通过pg_hba.conf配置;
- database权限:数据库权限通过grant和revoke操作schema配置;
- TBS权限:表空间权限通过grant和revoke操作表、物化视图、索引、临时表配置;
- schema权限:模式权限通过grant和revoke操作模式下的对象配置;
- object权限:对象权限通过grant和revoke配置;
GRANT create ON DATABASE lvzhenjiang TO lvzhenjiang;
ALTER SCHEMA abc OWNER to abc; GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc;
GRANT select,insert,update,delete ON a.b TO u;
postgres=# create database taobao; postgres=# \c taobao; taobao=# create SCHEMA miaosha; taobao=# create user miaosha with password '123'; taobao=# ALTER SCHEMA miaosha OWNER to miaosha; taobao=# GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA miaosha to miaosha;
\? \l \d \du \d t1 \c lvzhenjiang \help \help create user \du \x