Debian 11 上安装 postgresql
<h1 class="otl-heading">1.增加官方的Repository</h1><p>手动配置官方的 apt Repository</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;"># 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 https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'</pre>
</div>
<h1 class="otl-heading">2.更新系统包</h1>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ sudo apt update</pre>
</div>
<h1 class="otl-heading">3.安装和配置 postgresql 16</h1>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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
Get:1 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libjson-perl all 4.03000-1
Get:2 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 ssl-cert all 1.1.0+nmu1
Get:3 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libcommon-sense-perl amd64 3.75-1+b4
Get:4 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libtypes-serialiser-perl all 1.01-1
Get:5 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libjson-xs-perl amd64 4.030-1+b1
Get:6 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libz3-4 amd64 4.8.10-1
Get:7 http://mirrors.ustc.edu.cn/debian bullseye/main amd64 libllvm13 amd64 1:13.0.1-6~deb11u1
Get:8 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-client-common all 261.pgdg110+1
Get:9 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-common all 261.pgdg110+1
Get:10 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 libpq5 amd64 16.3-1.pgdg110+1
Get:11 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-client-16 amd64 16.3-1.pgdg110+1
Get:12 https://apt.postgresql.org/pub/repos/apt bullseye-pgdg/main amd64 postgresql-16 amd64 16.3-1.pgdg110+1
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) ...</pre>
</div>
<p>安装完成后会在/etc/下生成两个目录：postgresql、postgresql-common</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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</pre>
</div>
<p>版本检查</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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)</pre>
</div>
<p><code lang="plaintext">监听配置</code></p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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</pre>
</div>
<p>开启密码登录</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ sudosed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf</pre>
</div>
<p>将对等标识更改为信任</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ sudosed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf</pre>
</div>
<p>开启远程访问</p>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ sudovim /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</pre>
</div>
<h1 class="otl-heading">4.启动</h1>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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</pre>
</div>
<h1 class="otl-heading">5.修改超级用户的密码</h1>
<div class="cnblogs_Highlighter">
<pre class="brush:sql;gutter:true;">$ 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';
</pre>
</div>
<p> </p>
</div>
<div id="MySignature" role="contentinfo">
<b> <br></b>
<b> <br></b>
<img src="https://files.cnblogs.com/files/abclife/4.gif" width="400px" height="400px"><br>
<p>本文来自博客园，作者：abce，转载请注明原文链接：https://www.cnblogs.com/abclife/p/18281430</p><br><br>
来源：https://www.cnblogs.com/abclife/p/18281430
頁:
[1]