Postgres升级迁移

* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft memlock unlimited
* hard memlock unlimited
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited

systemctl stop firewalld.service
systemctl disable firewalld.service

vim /etc/sysctl.conf
fs.aio-max-nr=1048576
fs.file-max=76724600
fs.nr_open=20480000
kernel.sem=4096 2147483647 2147483646 512000
kernel.shmall=5033164
kernel.shmmax=20615843030
kernel.shmmni=819200
net.core.netdev_max_backlog=10000
net.core.wmem_max = 21299200
net.core.rmem_max = 21299200
net.core.wmem_default = 21299200
net.core.rmem_default = 21299200
net.core.somaxconn=65535
net.ipv4.tcp_max_tw_buckets=262144
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_retries1=5
net.ipv4.tcp_syn_retries=5
net.ipv4.tcp_synack_retries=5
net.ipv4.tcp_retries2=12
net.ipv4.tcp_rmem = 8192 250000 16777216
net.ipv4.tcp_wmem = 8192 250000 16777216
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_local_port_range=40000 65535
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_mem=8388608 12582912 16777216
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_sack=1
vm.swappiness=0
vm.zone_reclaim_mode=0
vm.numa_balancing=0
vm.extfrag_threshold = 500
vm.mim_free_kbytes=1048576
vm.overcommit_memory = 2
vm.overcommit_ratio=90
vm.dirty_ratio=10
vm.dirty_background_ratio=5
vm.dirty_writeback_centisecs=80
vm.mmap_min_addr=65536
vm.nr_hugepages=0
sysctl -p

groupadd postgres
useradd -g postgres postgres

mkdir -p /opt/pg1310
mkdir -p /data/{pgdata,archive,backup}
chown postgres.postgres /opt/pg1310 -R
chown postgres.postgres /data -R

vim /home/postgres/.bash_profile
umask 022
export PGHOME=/opt/pg1310
export PGPORT=5413
export PGDATA=/data/pgdata
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:/opt/repmgr/bin/:$PATH

vim /etc/ld.so.conf.d/pg1310.conf 加入以下内容
/opt/pg1310/lib
ldconfig #运行命令,加载新动态库到内存
ldconfig -p | grep -I postgres #查看新动态库是否加载

yum install -y rsync sysstat glib2 glib2-devel gcc gcc-c++ icu libicu 
libicu-devel flex flex-devel python python-devel tcl tcldevel perl perl-devel 
bison bison-devel perl-ExtUtils* readline readline-devel zlib zlib-devel 
openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt
libxslt-devel openldap openldap-devel ldapjdk ldapjdk-devel uuid 
uuid-devel systemd* bzip2 bzip2-devel bzip2-libs libevent 
libevent-devel libcurl libcurl-devel libyml libyml-devel libyaml
libyaml-devel libaio libaio-devel uuid uuiddevel logrotate lz4 
lz4-devel perl-Time-HiRes perl-DBI perl-DBD-Pg

su - postgres
tar -jxf postgresql-13.10.tar.bz2
cd postgresql-13.10
mkdir build
cd build

../configure --prefix=/opt/pg1310 --exec-prefix=/opt/pg1310 --enable-thread-safety --with-pgport=5413 \
--with-blocksize=8 --with-segsize=1 --with-wal-blocksize=8 --with-llvm --with-icu --with-tcl --with-perl --with-python \
--with-gssapi --with-pam --with-ldap --with-selinux --with-systemd --with-readline --with-libedit-preferred --with-ossp-uuid \
--with-libxml --with-libxslt --with-zlib --with-lz4 --with-gnu-ld --withopenssl
make
make world
make install
make install-world
make install-docs

initdb -D $PGDATA -E UTF8 -A MD5 -U postgres -W --wal-segsize=16 -k

# TYPE DATABASE USER ADDRESS METHOD

#"local" is for Unix domain socket connections only
local all             all                                     scram-sha-256
# IPv4 local connections:
host all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
host all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all                                     scram-sha-256
host replication all             0.0.0.0/0               scram-sha-256
host replication all             ::1/128                 scram-sha-256

include = 'pg_config.conf'

#Connection
listen_addresses = '*'
port = 5413
max_connections = 2500
superuser_reserved_connections = 5
unix_socket_directories = '/tmp/,.'
unix_socket_group = 'postgres'
unix_socket_permissions = 0700

#TCP settings
tcp_keepalives_idle = 60
tcp_keepalives_interval = 5
tcp_keepalives_count = 10
tcp_user_timeout = 0

#Authentication
authentication_timeout = 1min
password_encryption = scram-sha-256
db_user_namespace = off

#Resouce
shared_buffers = 2GB
huge_pages = try
temp_buffers = 4MB
max_prepared_transactions = 2500
work_mem = 4MB
maintenance_work_mem = 1024MB
autovacuum_work_mem = 2048MB
logical_decoding_work_mem = 128MB
max_stack_depth = 2MB
shared_memory_type = mmap
dynamic_shared_memory_type = posix
temp_file_limit = -1
max_files_per_process = 65535

vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 2
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 5000

#Background Writer
bgwriter_delay = 20ms
bgwriter_lru_maxpages = 5000
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 512kB

#Asynchronous Behavior
effective_io_concurrency = 200
maintenance_io_concurrency = 10
max_worker_processes = 8
max_parallel_maintenance_workers = 2
max_parallel_workers_per_gather = 2
parallel_leader_participation = on
max_parallel_workers = 4
old_snapshot_threshold = -1
backend_flush_after = 0

# WRITE-AHEAD LOG
wal_level = logical
fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
wal_compression = on
wal_log_hints = on
wal_init_zero = on
wal_recycle = on
wal_buffers = 16MB
wal_writer_delay = 20ms
wal_writer_flush_after = 1MB
wal_skip_threshold = 2MB
commit_delay = 10
commit_siblings = 5
checkpoint_timeout = 60min
max_wal_size = 12GB
min_wal_size = 3GB
checkpoint_completion_target = 0.95
checkpoint_flush_after = 256kB
checkpoint_warning = 1800s

archive_mode = on
archive_command = 'cp --sparse=always %p /data/pgdata/archive/%f'
archive_timeout = 3600

max_wal_senders = 10
wal_keep_size = 16GB
max_slot_wal_keep_size = -1
wal_sender_timeout = 60s
max_replication_slots = 10
track_commit_timestamp = on
synchronous_standby_names = ''
vacuum_defer_cleanup_age = 0
primary_conninfo = ''
primary_slot_name = ''
promote_trigger_file = ''
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_create_temp_slot = off
wal_receiver_status_interval = 10s
hot_standby_feedback = on
wal_receiver_timeout = 60s
wal_retrieve_retry_interval = 5s
recovery_min_apply_delay = 0
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_parallel_append = on
enable_seqscan = on
enable_sort = on
enable_incremental_sort = on
enable_tidscan = on
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
enable_parallel_hash = on
enable_partition_pruning = on

#Planner Cost Constants
seq_page_cost = 1.0
random_page_cost = 1.1
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_cache_size = 16GB
geqo = on
geqo_threshold = 12
geqo_effort = 5
geqo_pool_size = 0
geqo_generations = 0
geqo_selection_bias = 2.0
geqo_seed = 0.0
default_statistics_target = 500
constraint_exclusion = partition
cursor_tuple_fraction = 0.1
from_collapse_limit = 8
join_collapse_limit = 8
force_parallel_mode = off
jit = off
plan_cache_mode = auto

log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql_%a.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
syslog_sequence_numbers = on
syslog_split_messages = on
event_source = 'PostgreSQL'
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1s
log_min_duration_sample = -1
log_statement_sample_rate = 1.0
log_transaction_sample_rate = 0.0
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_error_verbosity = default
log_hostname = off
log_line_prefix = '%t [%p]:[%l-1] user=%u,db=%d,app=%a,client=%h'
log_lock_waits = on
log_parameter_max_length = -1
log_parameter_max_length_on_error = 0
log_statement = 'none'
log_replication_commands = off
log_temp_files = 0
log_timezone = 'PRC'
cluster_name = ''
update_process_title = on
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
track_activity_query_size = 2048
stats_temp_directory = 'pg_stat_tmp'
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_insert_threshold = 1000
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_insert_scale_factor = 0.01
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_cost_limit = 10000
client_min_messages = notice
search_path = '"$user", public'
row_security = on
default_tablespace = ''
temp_tablespaces = ''
default_table_access_method = 'heap'
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
default_transaction_deferrable = off
session_replication_role = 'origin'
statement_timeout = 30min
lock_timeout = 10s
idle_in_transaction_session_timeout = 30min
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
bytea_output = 'hex'
xmlbinary = 'base64'
xmloption = 'content'
gin_fuzzy_search_limit = 0
gin_pending_list_limit = 4MB

#Locale and Formatting
datestyle = 'iso, ymd'
intervalstyle = 'postgres'
timezone = 'PRC'
timezone_abbreviations = 'Default'
extra_float_digits = 1
client_encoding = sql_ascii
lc_messages = 'zh_CN.UTF-8'
lc_monetary = 'zh_CN.UTF-8'
lc_numeric = 'zh_CN.UTF-8'
lc_time = 'zh_CN.UTF-8'
default_text_search_config = 'pg_catalog.simple'
shared_preload_libraries = 'pg_stat_statements,repmgr'
pg_stat_statements.max=10000
pg_stat_statements.save=on
pg_stat_statements.track=top
pg_stat_statements.track_utility=on

local_preload_libraries = ''
session_preload_libraries = ''
jit_provider = 'llvmjit'
dynamic_library_path = '$libdir'

#LOCK MANAGEMENT
deadlock_timeout = 1s
max_locks_per_transaction = 128
max_pred_locks_per_transaction = 128
max_pred_locks_per_relation = -2
max_pred_locks_per_page = 2
array_nulls = on
backslash_quote = safe_encoding
escape_string_warning = on
lo_compat_privileges = off
operator_precedence_warning = off
quote_all_identifiers = off
standard_conforming_strings = on
synchronize_seqscans = on
transform_null_equals = off
exit_on_error = off
restart_after_crash = on
data_sync_retry = off

pg_ctl start -D $PGDATA -l /tmp/logfile

/data/webserver/postgresql/bin/pg_dumpall -h localhost -p5432 -U postgres > all.sql

psql -hlocalhost -p5413 -f all.sql

alter system set wal_level = logical;

pg_dump -p 5432 -d test -s > logical_upgrade_test.sql

SELECT n.nspname AS "Schema",c.relname AS "Table Name" FROM 
pg_catalog.pg_class c,pg_namespace n where c.relnamespace = n.oid 
AND n.nspname NOT IN ('information_schema', 'pg_catalog')
AND c.relkind='r' AND not exists (select 1 from pg_index i 
where c.oid=i.indrelid and i.indisprimary='t' ) ;

select 'alter table '||c.relname||' replica identity full;' FROM 
pg_catalog.pg_class c,pg_namespace n where c.relnamespace = n.oid 
AND n.nspname NOT IN ('information_schema', 'pg_catalog') 
AND c.relkind='r' AND not exists (select 1 from pg_index i 
where c.oid=i.indrelid and i.indisprimary='t' ) ;

alter table a replica identity default;

psql -p 5413 -d test -f logical_upgrade_test.sql

create user logical_upgrade_user replication login connection limit 4 encrypted password '1qaz@WSX..';
grant usage on schema public to logical_upgrade_user ;
grant select on all tables in schema public to logical_upgrade_user ;

create publication pub1 for all tables;

create subscription sub1 connection 'host=127.0.0.1 
port=5432 dbname=test user=logical_upgrade_user' publication pub1;

select pg_current_wal_lsn(); select replay_lsn from pg_stat_replication;

drop subscription sub1 cascade ;