1 无法删除用户
ERROR：角色不能被删除，因为有对象依赖于它
LINE 1：模式 public 的权限/用户下有表存在
解决方法：收回赋予此用户的 模式 public 的权限，并删除用户下的表，即可正常删除。
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
ljfz | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# drop user ljfz;
ERROR: role "ljfz" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table ta
postgres=#
postgres=# revoke usage on schema public from ljfz;
REVOKE
postgres=# drop user ljfz;
ERROR: role "ljfz" cannot be dropped because some objects depend on it
DETAIL: owner of table ta
postgres=#
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+----------
public | mydata | table | postgres
public | ta | table | ljfz
(2 rows)
postgres=# drop table ta;
DROP TABLE
postgres=# drop user ljfz;
DROP ROLE
postgres=#2 无法删除数据库
ERROR：其他用户正在访问数据库
LINE 1：还有一个使用数据库的会话
解决方法：KILL 会话相关的进程
[postgres@pgccc ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
subdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres+
| | | | | | | ljfz=c/postgres
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# drop database subdb;
ERROR: database "subdb" is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# \q
[postgres@pgccc ~]$ ps -axjf |grep postgres
19326 26702 26702 19326 pts/0 13158 S 0 0:00 | \_ su - postgres
19042 24317 24317 19042 pts/1 516 S 0 0:00 \_ su - postgres
24318 517 516 19042 pts/1 516 S+ 1000 0:00 \_ grep --color=auto postgres
1 13926 13926 13926 ? -1 Ss 1000 0:00 /pgccc/app/15.2/bin/postgres -D /pgccc/pgdata
13926 13927 13927 13927 ? -1 Ss 1000 0:00 \_ postgres: checkpointer
13926 13928 13928 13928 ? -1 Ss 1000 0:00 \_ postgres: background writer
13926 13930 13930 13930 ? -1 Ss 1000 0:00 \_ postgres: walwriter
13926 13931 13931 13931 ? -1 Ss 1000 0:00 \_ postgres: autovacuum launcher
13926 13932 13932 13932 ? -1 Ss 1000 0:00 \_ postgres: logical replication launcher
13926 2771 2771 2771 ? -1 Ss 1000 0:00 \_ postgres: ljfz subdb [local] idle
[postgres@pgccc ~]$ kill -9 2771
[postgres@pgccc ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# drop database subdb;
DROP DATABASE
postgres=#3 无法创建表
ERROR：schema public 的权限被拒绝。即权限缺失。
LINE 1：创建表... ...
解决方法：授权用户在 schema public 上的权限 create ，即可。（提示不能 create table，当然是缺少权限 create ）
[postgres@pgccc ~]$ psql
psql (15.2)
Type "help" for help.
postgres=#
postgres=# create user ljfz;
CREATE ROLE
postgres=# \c postgres ljfz
You are now connected to database "postgres" as user "ljfz".
postgres=> create table ta (id int);
ERROR: permission denied for schema public
LINE 1: create table ta (id int);
^
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant create on schema public to ljfz;
GRANT
postgres=# \c postgres ljfz
You are now connected to database "postgres" as user "ljfz".
postgres=> create table ta(id int);
CREATE TABLE
postgres=>4 大版本升级提示严重错误
提示：新的安装中缺少可加载的插件。你可以将插件从旧的集簇删除，也可将其添加到新集簇。
解决方法：查看下方提示的日志记录，提示缺少 file_fdw 。这里的处理是，将其从 旧集簇 删除
[postgres@pgccc ~]$ /pgccc/app/15.2/bin/pg_upgrade -b /pgccc/app/14.7/bin -B /pgccc/app/15.2/bin -d /pgccc/pgdata -D /pgccc/pgdata.new -c
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
/pgccc/pgdata.new/pg_upgrade_output.d/20230801T100522.997/loadable_libraries.txt
Failure, exiting
[postgres@pgccc ~]$ cat /pgccc/pgdata.new/pg_upgrade_output.d/20230801T100522.997/loadable_libraries.txt
could not load library "$libdir/file_fdw": ERROR: could not access file "$libdir/file_fdw": No such file or directory
In database: postgres
[postgres@pgccc ~]$ pg_start
waiting for server to start....2023-08-01 10:06:35.705 CST [28982] LOG: redirecting log output to logging collector process
2023-08-01 10:06:35.705 CST [28982] HINT: Future log output will appear in directory "log".
done
server started
[postgres@pgccc ~]$ psql
psql (14.7)
Type "help" for help.
postgres=# drop extension file_fdw;
ERROR: cannot drop extension file_fdw because other objects depend on it
DETAIL: server pg_log depends on foreign-data wrapper file_fdw
user mapping for postgres on server pg_log depends on server pg_log
foreign table pglog3 depends on server pg_log
foreign table passwd depends on server pg_log
foreign table pglog4 depends on server pg_log
foreign table pglog5 depends on server pg_log
HINT: Use DROP ... CASCADE to drop the dependent objects too.
postgres=#
postgres=# drop extension file_fdw CASCADE;
NOTICE: drop cascades to 6 other objects
DETAIL: drop cascades to server pg_log
drop cascades to user mapping for postgres on server pg_log
drop cascades to foreign table pglog3
drop cascades to foreign table passwd
drop cascades to foreign table pglog4
drop cascades to foreign table pglog5
DROP EXTENSION
postgres=#5 无法启动备库（基本备份）
FATAL：未启用备用模式时，必须指定restore_command
解决方法：设置 restore_command = 'cp /pgccc/archive/ %p'
[postgres@pgccc ~]$ pg_ctl -D pgdata_bak/ start -l logfile
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
[postgres@pgccc ~]$ cat logfile
2023-08-05 23:41:05.809 CST [32097] LOG: starting PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-05 23:41:05.810 CST [32097] LOG: listening on IPv4 address "0.0.0.0", port 5433
2023-08-05 23:41:05.810 CST [32097] LOG: listening on IPv6 address "::", port 5433
2023-08-05 23:41:05.811 CST [32097] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2023-08-05 23:41:05.816 CST [32098] LOG: database system was interrupted; last known up at 2023-08-05 23:31:44 CST
2023-08-05 23:41:05.843 CST [32098] FATAL: must specify restore_command when standby mode is not enabled
2023-08-05 23:41:05.844 CST [32097] LOG: startup process (PID 32098) exited with exit code 1
2023-08-05 23:41:05.844 CST [32097] LOG: aborting startup due to startup process failure
2023-08-05 23:41:05.846 CST [32097] LOG: database system is shut down
[postgres@pgccc ~]$6 基础备份无法生成
pg_basebackup:错误:连接到服务器“192.168.8.133”，端口1521失败:连接被拒绝。服务器是否在该主机上运行并接受TCP/IP连接?
解决方法：这里的错误指的并不是端口错误，而是指主库的 listen_addresses 的参数不是 * ，而是 locathost 造成。
[postgres@pgccc04 ~]$ pg_basebackup -D /pgccc/pgdata -Fp -R -P -v -h 192.168.8.133 -U lfz
pg_basebackup: error: connection to server at "192.168.8.133", port 1521 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
[postgres@pgccc04 ~]$ clear
[postgres@pgccc04 ~]$ pg_basebackup -D /pgccc/pgdata -Fp -R -P -v -h 192.168.8.133 -U lfz
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_101693"
26240/26240 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
[postgres@pgccc04 ~]$
