postgresql 常用操作笔记

将db数据结构复制到同库中的另外一张表（https://stackoverflow.com/questions/876522/creating-a-copy-of-a-database-in-postgresql、https://www.postgresqltutorial.com/postgresql-copy-database/）

pg_dump production-db | psql test-db

列出用户（https://www.postgresqltutorial.com/postgresql-list-users/）

SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;

配置文件pg_hba.conf配置、修改postgresql超级用户的密码 https://blog.csdn.net/skye1208/article/details/90406101

如何退出psql 命令行 https://stackoverflow.com/questions/9463318/how-to-exit-from-postgresql-command-line-utility-psql?rq=1

Type \q and then press ENTER to quit psql.

用户权限修改

alter role username with superuser

pg_hba.conf的一般路径

vi /etc/postgresql/版号/main/pg_hba.conf

例如配置一个可以远程访问的数据库，则在pg_hba.conf中添加

host     数据库名  用户    0.0.0.0/0               md5

修改之后，一般可以这样使配置生效

 service postgresql reload

PostgreSQL学习之【用户权限管理】说明  https://www.cnblogs.com/zhoujinyi/p/10939715.html

PostgreSQL基本配置与权限控制  https://blog.csdn.net/qq_23435961/article/details/111692602

• PostgreSQL主从分离

Django配置数据库读写分离   https://blog.csdn.net/Ayhan_huang/article/details/78784486

PostgreSQL主从复制部署 https://blog.csdn.net/wyl9527/article/details/84973474

Postgresql实现主从复制，读写分离 https://blog.csdn.net/qq_31905135/article/details/86689735

postresql 主键自增设置 https://blog.csdn.net/u011042248/article/details/49422305

CREATE SEQUENCE users_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
 
alter table users alter column id set default nextval('users_id_seq');