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=#