安装PostgreSQL12
# 添加PostgreSQL官方仓库
rambo@ub-ser-1:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt jammy-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 导入仓库签名
rambo@ub-ser-1:~$ curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/postgresql.gpg > /dev/null
rambo@ub-ser-1:~$ sudo apt update
rambo@ub-ser-1:~$ sudo apt install -y postgresql-12
Ubuntu下，默认用户是postgres，且该用户默认没有设置密码（只能用系统账户切换）：
rambo@ub-ser-1:~$ sudo -u postgres psql
# 设置密码
postgres=# ALTER USER postgres WITH PASSWORD 'Yourp@ssw0rd';
postgres=# \q
rambo@ub-ser-1:~$ sudo vim /etc/postgresql/12/main/pg_hba.conf
找到这一行（一般在顶部）：
local all postgres peer
改为：
local all postgres md5
rambo@ub-ser-1:~$ sudo systemctl restart postgresql@12-main
rambo@ub-ser-1:~$ psql -U postgres -W
Password:
psql (12.22 (Ubuntu 12.22-2.pgdg22.04+1))
Type "help" for help.
postgres=#
在第一个数据库里创建测试数据
rambo@ub-ser-1:~$ psql -U postgres -W
Password:
psql (12.22 (Ubuntu 12.22-2.pgdg22.04+1))
Type "help" for help.
# 创建测试库
postgres=# CREATE DATABASE testdb;
postgres=# \c testdb
Password:
You are now connected to database "testdb" as user "postgres".
# 创建测试表
testdb=# CREATE TABLE users (
id serial PRIMARY KEY,
name varchar(100),
age int
);
# 创建测试数据
testdb=#
INSERT INTO users (name, age) VALUES ('Alice0', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice1', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice2', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice3', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice4', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice5', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice6', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice7', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice8', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice9', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice10', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice11', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice12', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice13', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice14', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice15', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice16', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice17', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice18', 25), ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Alice19', 25), ('Bob', 30);
# 查询验证
testdb=# SELECT * FROM users;
testdb=# \q
# 这是原始数据
rambo@ub-ser-1:~$ sudo ls -alh /var/lib/postgresql/12/main/
total 92K
drwx------ 19 postgres postgres 4.0K Jun 9 10:49 .
drwxr-xr-x 3 postgres postgres 4.0K Jun 9 10:36 ..
drwx------ 6 postgres postgres 4.0K Jun 9 10:50 base
drwx------ 2 postgres postgres 4.0K Jun 9 10:49 global
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_dynshmem
drwx------ 4 postgres postgres 4.0K Jun 9 10:56 pg_logical
drwx------ 4 postgres postgres 4.0K Jun 9 10:36 pg_multixact
drwx------ 2 postgres postgres 4.0K Jun 9 10:49 pg_notify
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_replslot
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_serial
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_snapshots
drwx------ 2 postgres postgres 4.0K Jun 9 10:49 pg_stat
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_subtrans
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_tblspc
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_twophase
-rw------- 1 postgres postgres 3 Jun 9 10:36 PG_VERSION
drwx------ 3 postgres postgres 4.0K Jun 9 10:36 pg_wal
drwx------ 2 postgres postgres 4.0K Jun 9 10:36 pg_xact
-rw------- 1 postgres postgres 88 Jun 9 10:36 postgresql.auto.conf
-rw------- 1 postgres postgres 130 Jun 9 10:49 postmaster.opts
-rw------- 1 postgres postgres 108 Jun 9 10:49 postmaster.pid
准备第二个实例
rambo@ub-ser-1:~$ sudo mkdir -p /var/lib/postgresql/12_recover/
rambo@ub-ser-1:~$ sudo cp -rp /var/lib/postgresql/12/main/. /var/lib/postgresql/12_recover/
释义：
/var/lib/postgresql/12/main/* 复制所有非隐藏文件夹和文件，不包括以点开头的隐藏文件（如 .config，但一般Postgres目录下不常见）；
/var/lib/postgresql/12/main/. 表示复制目录下所有内容（包括隐藏文件夹），是推荐写法；
rambo@ub-ser-1:~$ sudo chown -R postgres:postgres /var/lib/postgresql/12_recover/
配置独立实例
# 生成一份新的postgresql.conf和pg_hba.conf
在相同版本（PostgreSQL 12）上新初始化一个空实例（不会破坏现有实例）
rambo@ub-ser-1:~$ sudo -u postgres /usr/lib/postgresql/12/bin/initdb -D /tmp/pg12_temp
rambo@ub-ser-1:~$ sudo ls -alh /tmp/pg12_temp/
drwx------ 5 postgres postgres 4.0K Jun 9 11:17 base
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 global
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_dynshmem
-rw------- 1 postgres postgres 4.7K Jun 9 11:17 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Jun 9 11:17 pg_ident.conf
drwx------ 4 postgres postgres 4.0K Jun 9 11:17 pg_logical
drwx------ 4 postgres postgres 4.0K Jun 9 11:17 pg_multixact
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_notify
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_replslot
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_serial
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_snapshots
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_stat
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_subtrans
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_tblspc
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_twophase
-rw------- 1 postgres postgres 3 Jun 9 11:17 PG_VERSION
drwx------ 3 postgres postgres 4.0K Jun 9 11:17 pg_wal
drwx------ 2 postgres postgres 4.0K Jun 9 11:17 pg_xact
-rw------- 1 postgres postgres 88 Jun 9 11:17 postgresql.auto.conf
-rw------- 1 postgres postgres 27K Jun 9 11:17 postgresql.conf
rambo@ub-ser-1:~$ sudo cp /tmp/pg12_temp/postgresql.conf /var/lib/postgresql/12_recover/
rambo@ub-ser-1:~$ sudo cp /tmp/pg12_temp/pg_hba.conf /var/lib/postgresql/12_recover/
rambo@ub-ser-1:~$ sudo cp /tmp/pg12_temp/pg_ident.conf /var/lib/postgresql/12_recover/
rambo@ub-ser-1:~$ sudo chown postgres:postgres /var/lib/postgresql/12_recover/postgresql.conf
rambo@ub-ser-1:~$ sudo chown postgres:postgres /var/lib/postgresql/12_recover/pg_hba.conf
rambo@ub-ser-1:~$ sudo chown postgres:postgres /var/lib/postgresql/12_recover/pg_ident.conf
删除错误的postmaster.pid
rambo@ub-ser-1:~$ sudo rm -f /var/lib/postgresql/12_recover/postmaster.pid
rambo@ub-ser-1:~$ sudo vim /var/lib/postgresql/12_recover/postgresql.conf
port = 5433 # 新实例单独用5433端口，避免与现有5432冲突
data_directory = '/var/lib/postgresql/12_recover' # 指定数据目录
unix_socket_directories = '/var/run/postgresql' # 避免socket冲突
listen_addresses = 'localhost' # 仅本机访问
rambo@ub-ser-1:~$ sudo vim /var/lib/postgresql/12_recover/pg_hba.conf
加入以下内容（放最前面即可），意为允许本机登录，不用密码（测试临时用途）
local all all trust
host all all 127.0.0.1/32 trust
# 手动启动新实例
rambo@ub-ser-1:~$ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12_recover/ restart
could not change directory to "/home/rambo": Permission denied
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-06-09 11:27:55.089 UTC [7445] LOG: starting PostgreSQL 12.22 (Ubuntu 12.22-2.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2025-06-09 11:27:55.089 UTC [7445] LOG: listening on IPv4 address "127.0.0.1", port 5433
2025-06-09 11:27:55.092 UTC [7445] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2025-06-09 11:27:55.113 UTC [7446] LOG: database system was shut down at 2025-06-09 11:27:54 UTC
2025-06-09 11:27:55.120 UTC [7445] LOG: database system is ready to accept connections
done
server started
rambo@ub-ser-1:~$ sudo netstat -anpt|egrep 543'(2|3)'
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7035/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 7445/postgres
# 创建专属 systemd 单元文件
rambo@ub-ser-1:~$ sudo vim /etc/systemd/system/postgresql-12-recover.service
[Unit]
Description=PostgreSQL 12 Recover Instance
After=network.target
[Service]
Type=simple
User=postgres
Group=postgres
# 指定新data目录
ExecStart=/usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12_recover
ExecStop=/usr/lib/postgresql/12/bin/pg_ctl stop -D /var/lib/postgresql/12_recover
ExecReload=/usr/lib/postgresql/12/bin/pg_ctl reload -D /var/lib/postgresql/12_recover
# 确保PID文件与data目录一致
PIDFile=/var/lib/postgresql/12_recover/postmaster.pid
# 安全性
Restart=on-failure
[Install]
WantedBy=multi-user.target
rambo@ub-ser-1:~$ sudo mkdir -p /var/run/postgresql
rambo@ub-ser-1:~$ sudo chown postgres:postgres /var/run/postgresql
rambo@ub-ser-1:~$ sudo chmod 2775 /var/run/postgresql
rambo@ub-ser-1:~$ sudo systemctl restart postgresql-12-recover
# 重新加载systemd配置
rambo@ub-ser-1:~$ sudo systemctl daemon-reload
rambo@ub-ser-1:~$ sudo systemctl restart postgresql-12-recover
rambo@ub-ser-1:~$ sudo systemctl enable postgresql-12-recover
rambo@ub-ser-1:~$ sudo systemctl status postgresql-12-recover
● postgresql-12-recover.service - PostgreSQL 12 Recover Instance
Loaded: loaded (/etc/systemd/system/postgresql-12-recover.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2025-06-09 11:40:58 UTC; 1s ago
Main PID: 7790 (postgres)
Tasks: 7 (limit: 4520)
Memory: 17.0M
CPU: 65ms
CGroup: /system.slice/postgresql-12-recover.service
├─7790 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12_recover
├─7793 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─7794 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" """ "" "" "" "" "" "" "" "" "" "" "" ""
├─7795 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─7796 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """""""""" "" "" "" "" ""
├─7797 "postgres: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
└─7798 "postgres: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """""""""" "" ""
Jun 09 11:40:58 ub-ser-1 systemd[1]: Started PostgreSQL 12 Recover Instance.
Jun 09 11:40:58 ub-ser-1 postgres[7790]: 2025-06-09 11:40:58.234 UTC [7790] LOG: starting PostgreSQL 12.22 (Ubuntu 12.22-2.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled >
Jun 09 11:40:58 ub-ser-1 postgres[7790]: 2025-06-09 11:40:58.235 UTC [7790] LOG: listening on IPv4 address "127.0.0.1", port 5433
Jun 09 11:40:58 ub-ser-1 postgres[7790]: 2025-06-09 11:40:58.237 UTC [7790] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
Jun 09 11:40:58 ub-ser-1 postgres[7792]: 2025-06-09 11:40:58.257 UTC [7792] LOG: database system was shut down at 2025-06-09 11:40:58 UTC
Jun 09 11:40:58 ub-ser-1 postgres[7790]: 2025-06-09 11:40:58.263 UTC [7790] LOG: database system is ready to accept connections
从5433(恢复实例)导出testdb再导入到5432实例
rambo@ub-ser-1:~$ sudo -u postgres /usr/lib/postgresql/12/bin/pg_dump -p 5433 -F c -b -v -f /tmp/testdb.dump testdb
释义：
-p 5433：连接临时恢复实例；
-F c：Custom格式，适合用pg_restore；
/tmp/testdb.dump：导出路径
rambo@ub-ser-1:~$ ls -alh /tmp/testdb.dump
-rw-rw-r-- 1 postgres postgres 2.8K Jun 9 11:47 /tmp/testdb.dump
# 将 testdb.dump 导入系统PostgreSQL（5432）
确认5432实例是否有目标库
rambo@ub-ser-1:~$ sudo -u postgres psql -p 5432 -c "\l"
could not change directory to "/home/rambo": Permission denied
Password for user postgres:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
====================================================================
如果目标数据库（例如也叫testdb111）不存在，则新建：
rambo@ub-ser-1:~$ sudo -u postgres createdb -p 5432 testdb111
rambo@ub-ser-1:~$ sudo -u postgres psql -p 5432 -c "\l"
could not change directory to "/home/rambo": Permission denied
Password for user postgres:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb111 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
====================================================================
导入数据
rambo@ub-ser-1:~$ sudo -u postgres pg_restore -p 5432 -d testdb111 /tmp/testdb.dump
注：如5432已经有testdb111且不希望覆盖，请换个数据库名或先删除原库
# 确认数据成功导入进去
# 方法1
rambo@ub-ser-1:~$ sudo -u postgres psql -p 5432 -d testdb111 -c "SELECT count(*) FROM users;"
could not change directory to "/home/rambo": Permission denied
Password for user postgres: # 密码是 Yourp@ssw0rd
count
-------
40 # 有40条数据
(1 row)
# 方法2
rambo@ub-ser-1:~$ sudo -u postgres psql -p 5432 -d testdb111
could not change directory to "/home/rambo": Permission denied
Password for user postgres:
psql (12.22 (Ubuntu 12.22-2.pgdg22.04+1))
Type "help" for help.
testdb111=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | users | table | postgres
(1 row)
testdb111=# \q
# 查看表中的前10条数据
rambo@ub-ser-1:~$ sudo -u postgres psql -p 5432 -d testdb111 -c "SELECT * FROM users LIMIT 10;"
could not change directory to "/home/rambo": Permission denied
Password for user postgres:
id | name | age
----+--------+-----
1 | Alice0 | 25
2 | Bob | 30
3 | Alice1 | 25
4 | Bob | 30
5 | Alice2 | 25
6 | Bob | 30
7 | Alice3 | 25
8 | Bob | 30
9 | Alice4 | 25
10 | Bob | 30
(10 rows)
rambo@ub-ser-1:~$ sudo netstat -anpt | egrep 543'(2|3)'
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7035/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 7790/postgres
关闭5433（临时恢复实例）
rambo@ub-ser-1:~$ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12_recover stop
could not change directory to "/home/rambo": Permission denied
waiting for server to shut down.... done
server stopped
或者
rambo@ub-ser-1:~$ sudo netstat -anpt | egrep 543'(2|3)'
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7035/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 8045/postgres
rambo@ub-ser-1:~$ sudo systemctl stop postgresql-12-recover
# 确认已关闭
rambo@ub-ser-1:~$ sudo netstat -anpt | egrep 543'(2|3)'
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7035/postgres
浙公网安备 33010602011771号