blog/content/middleware/postgresql_in_short.md

---

title: "Postgresql 操作" date: 2022-10-12T15:06:02+07:00

draft: true

基本环境

• OS: Manjaro(Arch Serial)
• Postgresql: v14.5.1

手册

https://github.com/postgres-cn/pgdoc-cn

安装

1. arch linux 安裝

   pacman -Syu
   pacman -S postgresql
   

   1. 運行 ```shell sudo su - postgres initdb --locale en_US.UTF-8 -D /var/lib/postgres/data exit # quit postgres user shell

   sudo su - postgres createdb createuser dropdb dropuser

   以上命令或者 sudo -u postgres createdb

   ```

   sudo -u postgres psql # enter psql
   alter user <username> with encrypted password 'password';
   grant all privileges on database <database> to <username>;
   

   sudo su - postgres
   create user <username> with password 'password';
   create database <database> owner <username>;
   grant all privileges on database <database> to <username>;
   \q
   

   1. 命令 ```shell
   2. \h SQL help
   3. \? psql help
   4. \l show all databases;
   5. \c connect to
   6. \d list all tables;
   7. \du list all users
   8. \e open editor

   9. \conninfo: list database and connection info

      ### 
      #### 升级问题
      
      滚动更新后会更新 Postgresql 到新版本，再次启动 `systemctl restart postgresql` 后可能会报错 `An old version of the database format was found.` 导致启动失败。解决如下
      
      https://webhostinggeeks.com/howto/how-to-fix-an-old-version-of-the-database-format-was-found-while-starting-postgresql/
      
      升级数据
      https://suay.site/?p=1130
      
      ```shell
      
      

   stream replica https://girders.org/postgresql/2021/11/05/setup-postgresql14-replication/

   logic replica https://hevodata.com/learn/postgresql-master-slave-replication/#intro

   Connection pool https://github.com/yandex/odyssey https://pgpool.net/mediawiki/index.php/Main_Page

   Replication extension https://github.com/2ndQuadrant/pglogical

   Export Import https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/importing-and-exporting-data-in-postgresql#data-export-with-pg_dump