postgres 源码安装
shell
#安装前置依赖
yum install -y readline-devel
yum install -y zlib-devel
yum install -y gcc-c++
yum install -y perl-ExtUtils-Embed
yum install zlib zlib-devel
yum install python-devel
# 进入源码目录 执行命令
./configure --prefix=/usr/local/pgsql14.7 --with-perl --with-python
make
make install
# 设置软连接
ln -sf /usr/local/pgsql14.7 /usr/local/pgsql
# 配置环境变量 /etc/profile
export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
source /etc/profile
# 创建用户
useradd postgres
# 设置密码 12345678
passwd postgres
# 若要赋予该用户sudo权限（可选，取决于你希望mydba用户是否具有管理员权限）
sudo usermod -aG wheel postgres
# 确认用户和组信息已生效
id postgres
mkdir -p /home/postgres/pgdata
chown -R postgres:postgres /home/postgres/
# 将目录权限设置为700，这意味着仅属主（这里是postgres用户）有权读取、写入和执行目录内的内容，其他用户无权访问，
#这是出于数据库安全性的考虑。
chmod -R 700 /home/postgres/
# 配置环境变量 /etc/profile
export PGDATA=/home/postgres/pgdata
source /etc/profile
##################
##########安装contrib目录下的工具
cd /opt/postgresql-14.7/contrib
make
make install
# 登录 postgres
su - postgres
# 初始化数据库
initdb
# 启动
pg_ctl start -D $PGDATA
pg_ctl start -D /home/postgres/pgdata
pg_ctl -D /home/postgres/pgdata -l logfile start
# 停止
pg_ctl stop -D $PGDATA -m fast
pg_ctl stop -D $PGDATA -m smart
pg_ctl stop -D $PGDATA -m immediate
# 登录数据库 设置密码
su - postgres
# 指定数据库 用户 登录
psql -U postgres -d postgres
# 修改用户密码a
ALTER USER postgres WITH PASSWORD '123456';
# 创建用户
CREATE USER isoft WITH PASSWORD '123456';
#把数据库授权给用户
GRANT ALL PRIVILEGES ON DATABASE test TO isoft;
# 创建数据库
create database test;
# 登录数据库
psql -h localhost -p 5432 -U postgres -d postgres
# 切换数据库 use test;
\c
# show tables
\dt
# show databases;
\l
# desc table
\d
# 格式化输出
\x
# 授权 给用户
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC to isoft;
# 授权连接的权限
GRANT CONNECT ON DATABASE snowy TO isoft;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE sys_user TO isoft;备份
sh
## 需要输入密码
pg_dump -U postgres -W -F p gitea > gitea.sql
## 不需要输入密码
pg_dump -U postgres -w -F p gitea > gitea2.sql
pg_dump -U postgres -W -F p gitea | gzip > gitea.gz
pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -F p gitea > mydatabase.dump
## -U 用户 -d 数据库名称 -F t -f 文件名
## c|d|t|p output file format (custom, directory, tar, plain text (default))
pg_dump -U postgres -d gitea -F t -f data.tar
## 恢复 tar 包
pg_restore -U postgres -d test data.tar
create database gitea;
pg_restore -C -U postgres -d gitea -h 127.0.0.1 ./gitea.sql
pg_restore -C -U postgres -d gitea -h 127.0.0.1 ./gitea.gz
gunzip -c ./gitea.gz | psql -U postgres -d gitea
########################################################
-d, --dbname=DBNAME database to dump
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump
##############################
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-b, --large-objects include large objects in dump
--blobs (same as --large-objects, deprecated)
-B, --no-large-objects exclude large objects in dump
--no-blobs (same as --no-large-objects, deprecated)
-c, --clean clean (drop) database objects before recreating
-C, --create include commands to create database in dump
-e, --extension=PATTERN dump the specified extension(s) only
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=PATTERN dump the specified schema(s) only
-N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
-O, --no-owner skip restoration of object ownership in
plain-text format
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME superuser user name to use in plain-text format
-t, --table=PATTERN dump only the specified table(s)
-T, --exclude-table=PATTERN do NOT dump the specified table(s)
-x, --no-privileges do not dump privileges (grant/revoke)
--binary-upgrade for use by upgrade utilities only
--column-inserts dump data as INSERT commands with column names
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security (dump only content user has
access to)
--exclude-table-and-children=PATTERN
do NOT dump the specified table(s), including
child and partition tables
--exclude-table-data=PATTERN do NOT dump data for the specified table(s)
--exclude-table-data-and-children=PATTERN
do NOT dump data for the specified table(s),
including child and partition tables
--extra-float-digits=NUM override default setting for extra_float_digits
--if-exists use IF EXISTS when dropping objects
--include-foreign-data=PATTERN
include data of foreign tables on foreign
servers matching PATTERN
--inserts dump data as INSERT commands, rather than COPY
--load-via-partition-root load partitions via the root table
--no-comments do not dump comments
--no-publications do not dump publications
--no-security-labels do not dump security label assignments
--no-subscriptions do not dump subscriptions
--no-table-access-method do not dump table access methods
--no-tablespaces do not dump tablespace assignments
--no-toast-compression do not dump TOAST compression methods
--no-unlogged-table-data do not dump unlogged table data
--on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands
--quote-all-identifiers quote all identifiers, even if not key words
--rows-per-insert=NROWS number of rows per INSERT; implies --inserts
--section=SECTION dump named section (pre-data, data, or post-data)
--serializable-deferrable wait until the dump can run without anomalies
--snapshot=SNAPSHOT use given snapshot for the dump
--strict-names require table and/or schema include patterns to
match at least one entity each
--table-and-children=PATTERN dump only the specified table(s), including
child and partition tables
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownershipubuntu postgres
sh
sudo apt install postgresql
## 配置文件和数据存储目录
/etc/postgresql/16/main
## postgresql.conf 添加设置项
listen_addresses = '*'
## pg_hba.conf 新增一条登录设置 允许远程登录
host all postgres 0.0.0.0/0 md5
## 通过 psql 修改密码
ALTER USER postgres WITH PASSWORD '12345678';
## 远程登录 -U username -d dbname -h localhost
psql -U postgres -d test -h 127.0.0.1
psql -U postgres -d postgres -h 172.17.68.223postgresql 安装
sh
# Install the repository RPM:
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-server
/usr/pgsql-14/bin/postgresql-14-setup initdb
systemctl start postgresql-14
systemctl enable postgresql-14
############ 15
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql15-server
/usr/pgsql-15/bin/postgresql-15-setup initdb
systemctl restart postgresql-15
systemctl enable postgresql-15pg 15 主从搭建
sh
https://zhuanlan.zhihu.com/p/700043805
## master 服务器
1.创建用户并赋予复制权限：
CREATE ROLE repl LOGIN REPLICATION ENCRYPTED PASSWORD 'repl';
2.添加配置：vi /var/lib/pgsql/15/data/pg_hba.conf
# 允许远程链接
host all all 0.0.0.0/0 md5
# 允许从库以复制用户链接
host replication repl 121.40.20.28(从库ip)/32 md5
3.
vi /var/lib/pgsql/15/data/postgresql.conf
listen_addresses = '*' # 设置监听的ip，* 为允许所有
wal_level = replica # wal日志写入级别，要使用流复制，必须使用replica或更高级别
full_page_writes = on # 可以防止意外宕机后部分数据无法写入
synchronous_commit = on
synchronous_standby_names = '*'
hot_standby = on # 打开热备
vi /var/lib/pgsql/15/data/postgresql.auto.conf
## 添加 主服务器信息
primary_conninfo = 'user=repl password=123456 channel_binding=prefer host=172.17.68.222 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
4. 重启
systemctl restart postgresql-15.service
## 从库
rm -rf var/lib/pgsql/15/data
## 启动主从 备份
pg_basebackup -h 172.17.68.222 -p 5432 -U repl --password -X stream -Fp --progress -D /var/lib/pgsql/15/data -R
##########################
1.主库使用 pg_stat_replication 监控流复制：
select * from pg_stat_replication;
2.从库使用 pg_stat_wal_receiver 监控流复制：
select * from pg_stat_wal_receiver;
3.查看备库落后主库多少字节的WAL日志：
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(), write_lsn)) delay_wal_size, * from pg_stat_replication;
4.查看备库接收WAL日志和应用WAL日志的状态：
select * from pg_last_wal_receive_lsn();
select * from pg_last_wal_replay_lsn();
select * from pg_last_xact_replay_timestamp();