Debian 11 上安装 postgresql

1.增加官方的Repository

手动配置官方的 apt Repository

?

1 2 3 4 5 6 7

# Import the repository signing key: $ sudo apt install curl ca-certificates $ sudo install -d /usr/share/postgresql-common/pgdg $ sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc   # Create the repository configuration file: $ sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

2.更新系统包

?

1	$ sudo apt update

3.安装和配置 postgresql 16

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108

$ sudo apt install postgresql-16 Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed:   libcommon-sense-perl libjson-perl libjson-xs-perl libllvm13 libpq5 libtypes-serialiser-perl libz3-4 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert Suggested packages:   postgresql-doc-16 The following NEW packages will be installed:   libcommon-sense-perl libjson-perl libjson-xs-perl libllvm13 libpq5 libtypes-serialiser-perl libz3-4 postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert 0 upgraded, 12 newly installed, 0 to remove and 101 not upgraded. Need to get 47.3 MB of archives. After this operation, 193 MB of additional disk space will be used. Do you want to continue? [Y/n] y Get:1 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libjson-perl all 4.03000-1 [88.6 kB] Get:2 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 ssl-cert all 1.1.0+nmu1 [21.0 kB] Get:3 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libcommon-sense-perl amd64 3.75-1+b4 [24.6 kB] Get:4 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libtypes-serialiser-perl all 1.01-1 [12.2 kB] Get:5 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libjson-xs-perl amd64 4.030-1+b1 [94.9 kB] Get:6 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libz3-4 amd64 4.8.10-1 [6,949 kB] Get:7 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libllvm13 amd64 1:13.0.1-6~deb11u1 [20.1 MB] Get:8 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-client-common all 261.pgdg110+1 [94.7 kB] Get:9 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-common all 261.pgdg110+1 [240 kB] Get:10 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 libpq5 amd64 16.3-1.pgdg110+1 [217 kB] Get:11 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-client-16 amd64 16.3-1.pgdg110+1 [1,901 kB]                                                                                                                          Get:12 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-16 amd64 16.3-1.pgdg110+1 [17.5 MB]                                                                                                                                  Fetched 47.3 MB in 59s (798 kB/s)                                                                                                                                                                                                                        Preconfiguring packages ... Selecting previously unselected package libjson-perl. (Reading database ... 44352 files and directories currently installed.) Preparing to unpack .../00-libjson-perl_4.03000-1_all.deb ... Unpacking libjson-perl (4.03000-1) ... Selecting previously unselected package postgresql-client-common. Preparing to unpack .../01-postgresql-client-common_261.pgdg110+1_all.deb ... Unpacking postgresql-client-common (261.pgdg110+1) ... Selecting previously unselected package ssl-cert. Preparing to unpack .../02-ssl-cert_1.1.0+nmu1_all.deb ... Unpacking ssl-cert (1.1.0+nmu1) ... Selecting previously unselected package postgresql-common. Preparing to unpack .../03-postgresql-common_261.pgdg110+1_all.deb ... Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' Unpacking postgresql-common (261.pgdg110+1) ... Selecting previously unselected package libcommon-sense-perl. Preparing to unpack .../04-libcommon-sense-perl_3.75-1+b4_amd64.deb ... Unpacking libcommon-sense-perl (3.75-1+b4) ... Selecting previously unselected package libtypes-serialiser-perl. Preparing to unpack .../05-libtypes-serialiser-perl_1.01-1_all.deb ... Unpacking libtypes-serialiser-perl (1.01-1) ... Selecting previously unselected package libjson-xs-perl. Preparing to unpack .../06-libjson-xs-perl_4.030-1+b1_amd64.deb ... Unpacking libjson-xs-perl (4.030-1+b1) ... Selecting previously unselected package libz3-4:amd64. Preparing to unpack .../07-libz3-4_4.8.10-1_amd64.deb ... Unpacking libz3-4:amd64 (4.8.10-1) ... Selecting previously unselected package libllvm13:amd64. Preparing to unpack .../08-libllvm13_1%3a13.0.1-6~deb11u1_amd64.deb ... Unpacking libllvm13:amd64 (1:13.0.1-6~deb11u1) ... Selecting previously unselected package libpq5:amd64. Preparing to unpack .../09-libpq5_16.3-1.pgdg110+1_amd64.deb ... Unpacking libpq5:amd64 (16.3-1.pgdg110+1) ... Selecting previously unselected package postgresql-client-16. Preparing to unpack .../10-postgresql-client-16_16.3-1.pgdg110+1_amd64.deb ... Unpacking postgresql-client-16 (16.3-1.pgdg110+1) ... Selecting previously unselected package postgresql-16. Preparing to unpack .../11-postgresql-16_16.3-1.pgdg110+1_amd64.deb ... Unpacking postgresql-16 (16.3-1.pgdg110+1) ... Setting up postgresql-client-common (261.pgdg110+1) ... Setting up libpq5:amd64 (16.3-1.pgdg110+1) ... Setting up libcommon-sense-perl (3.75-1+b4) ... Setting up libz3-4:amd64 (4.8.10-1) ... Setting up ssl-cert (1.1.0+nmu1) ... Setting up libllvm13:amd64 (1:13.0.1-6~deb11u1) ... Setting up libtypes-serialiser-perl (1.01-1) ... Setting up libjson-perl (4.03000-1) ... Setting up libjson-xs-perl (4.030-1+b1) ... Setting up postgresql-client-16 (16.3-1.pgdg110+1) ... update-alternatives: using /usr/share/postgresql/16/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-common (261.pgdg110+1) ... Adding user postgres to group ssl-cert   Creating config file /etc/postgresql-common/createcluster.conf with new version Building PostgreSQL dictionaries from installed myspell/hunspell packages... Removing obsolete dictionary files: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service. Setting up postgresql-16 (16.3-1.pgdg110+1) ... Creating new PostgreSQL cluster 16/main ... /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions The files belonging to this database system will be owned by user "postgres". This user must also own the server process.   The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english".   Data page checksums are disabled.   fixing permissions on existing directory /var/lib/postgresql/16/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Shanghai creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Processing triggers for man-db (2.9.4-2) ... Processing triggers for libc-bin (2.31-13+deb11u5) ...

安装完成后会在/etc/下生成两个目录：postgresql、postgresql-common

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

$ sudo tree postgresql postgresql └── 16     └── main         ├── conf.d         ├── environment         ├── pg_ctl.conf         ├── pg_hba.conf         ├── pg_ident.conf         ├── postgresql.conf         └── start.conf   3 directories, 6 files $ sudo tree postgresql-common/ postgresql-common/ ├── createcluster.conf ├── pg_upgradecluster.d │   └── analyze ├── root.crt ├── supported_versions └── user_clusters   1 directory, 5 files

版本检查

?

1 2 3 4 5 6 7

$ sudo psql --version psql (PostgreSQL) 16.3 (Debian 16.3-1.pgdg110+1) root@test1:/etc/postgresql/16/main# sudo -u postgres psql -c "select version();"                                                            version                                                           -----------------------------------------------------------------------------------------------------------------------------  PostgreSQL 16.3 (Debian 16.3-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row)

监听配置

?

1 2 3 4 5 6 7 8 9 10

$ sudo vim /etc/postgresql/16/main/postgresql.conf #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------   # - Connection Settings -   listen_addresses = '*'          # what IP address(es) to listen on;                                         # comma-separated list of addresses;                                         # defaults to 'localhost'; use '*' for al

开启密码登录

?

1	$ sudo  sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf

将对等标识更改为信任

?

1	$ sudo  sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf

开启远程访问

?

1 2 3 4 5 6 7

$ sudo  vim /etc/postgresql/16/main/pg_hba.conf # IPv4 local connections: host    all             all             127.0.0.1/32            scram-sha-256 host    all             all             0.0.0.0/0               md5 # IPv6 local connections: host    all             all             ::1/128                 scram-sha-256 host    all             all             0.0.0.0/0               md5

4.启动

?

1 2 3 4

$ sudo systemctl start postgresql && sudo systemctl enable postgresql Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable postgresql $ sudo  /lib/systemd/systemd-sysv-install enable postgresql

5.修改超级用户的密码

?

1 2 3 4 5 6

$ sudo -i -u postgres postgres@test1:~$ psql psql (16.3 (Debian 16.3-1.pgdg110+1)) Type "help" for help.   postgres=# ALTER USER postgres PASSWORD 'abcd';