ubuntu22下默认安装postgresql16

# 一、官网地址

# PostgreSQL的官方地址：https://www.postgresql.org

# PostgreSQL的国内社区：http://www.postgres.cn/v2/home

# 编译后的程序下载地址：

# https://www.postgresql.org/download/

# 编译源代码下载地址

# https://www.postgresql.org/ftp/source/

# 使用默认安装方式，获取最新版本安装

# 1、创建文件存储库配置：

cd /etc/apt/sources.list.d

ls -al

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 2、导入存储库签名密钥：

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 3、更新包列表：

sudo apt-get update

# 4、安装最新版本的 PostgreSQL。

# 如果您想要特定版本，请使用“postgresql-12”或类似版本而不是“postgresql”：

sudo apt-get -y install postgresql

# Ubuntu 默认包含 PostgreSQL。要在 Ubuntu 上安装 PostgreSQL，请使用apt-get（或其他 apt-driven）命令：

# apt-get install postgresql-12

# 该存储库包含许多不同的软件包，包括第三方插件。最常见和最重要的软件包是（根据需要替换版本号）：

# postgresql-client-12  客户端库和客户端二进制文件

# postgresql-12 核心数据库服务器

# libpq-dev C语言前端开发的库和头文件

# postgresql-server-dev-12  C语言后端开发的库和头文件

# 5、验证

# 前面已经安装完成，接下来验证下是否安装成功

# 以超级用户root身份登录到PostgreSQL数据库：

sudo -u postgres psql

# PostgreSQL不推荐使用root管理，在安装成功postgreSQL后，他默认会给你创建一个postgres系统管理用户：postgres，无密码，可以修改密码：

sudo passwd postgres

# 两次输入密码，我与root用户密码相同

# 再次使用该用户

su - postgres

# 进入psql

psql

# 查看有哪些库：\l，如下，有三个库，分别是postgres，template0，template1

\l

# 退出PostgreSQL：\q

\q

# postgres安装后，默认分配一个数据库管理的超级角色用户，默认用户名也是postgre

# 系统用户表：pg_user

# 系统角色表：pg_roles

select rolname from pg_roles;

select usename from pg_user;

# 修改数据库用户密码postgres

# ALTER USER postgres WITH PASSWORD '新密码'

ALTER USER postgres WITH PASSWORD '123456'

# 6、配置

# 切换系统postgres用户

su postgres

# 进入目录： cd /var/lib/postgresql/16/main ，基本配置都在这个目录下：

cd /var/lib/postgresql/16/main

ls -al

<!--

total 92

drwx------ 19 postgres postgres 4096  3月 31 21:30 .

drwxr-xr-x  3 postgres postgres 4096  3月 31 21:30 ..

drwx------  5 postgres postgres 4096  3月 31 21:30 base

drwx------  2 postgres postgres 4096  3月 31 21:48 global

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_commit_ts

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_dynshmem

drwx------  4 postgres postgres 4096  3月 31 21:35 pg_logical

drwx------  4 postgres postgres 4096  3月 31 21:30 pg_multixact

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_notify

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_replslot

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_serial

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_snapshots

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_stat

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_stat_tmp

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_subtrans

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_tblspc

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_twophase

-rw-------  1 postgres postgres    3  3月 31 21:30 PG_VERSION

drwx------  3 postgres postgres 4096  3月 31 21:30 pg_wal

drwx------  2 postgres postgres 4096  3月 31 21:30 pg_xact

-rw-------  1 postgres postgres   88  3月 31 21:30 postgresql.auto.conf

-rw-------  1 postgres postgres  130  3月 31 21:30 postmaster.opts

-rw-------  1 postgres postgres  108  3月 31 21:30 postmaster.pid

-->

# 从上可以看到，postgreSQL的核心文件，都属于postgres用户，所以，操作的时候，尽可能的别用root用户，容易出错，尽可能先切换到postgres用户，再去操作。

<!--

# 常用命令

\? #所有命令帮助

\c [database_name] #切换到指定的数据库

\c #显示当前数据库名称和用户

\conninfo #显示客户端的连接信息

\d #列出数据库中所有表

\d+ #列出数据库中所有表和详细信息

\dt #列出数据库中所有表

\d [table_name] #显示指定表的结构

\di #列出数据库中所有 index

\dv #列出数据库中所有 view

\du #显示所有用户

\dn #显示数据库中的schema

\h #sql命令帮助

\pset border 0|1|2 #更改查询结果边框显示风格

\q #退出连接

\set [参数名] [参数值] #修改参数，参数名大小写敏感；不加参数，则显示当前参数；

\timing #显示命令执行消耗的时间

\l #列出所有数据库

\encoding #显示字符集

\i testdb.sql #执行sql文件

\x #扩展展示结果信息，相当于MySQL的\G

\o /tmp/t1.txt #将下一条sql执行结果导入文件中

-->

<!--

# 安装日志如下：

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 libpq5

  libtypes-serialiser-perl postgresql-16 postgresql-client-16

  postgresql-client-common postgresql-common sysstat

Suggested packages:

  postgresql-doc postgresql-doc-16 isag

The following NEW packages will be installed:

  libcommon-sense-perl libjson-perl libjson-xs-perl libpq5

  libtypes-serialiser-perl postgresql postgresql-16 postgresql-client-16

  postgresql-client-common postgresql-common sysstat

0 upgraded, 11 newly installed, 0 to remove and 0 not upgraded.

Need to get 21.3 MB of archives.

After this operation, 72.7 MB of additional disk space will be used.

Get:1 http://mirrors.tuna.tsinghua.edu.cn/ubuntu jammy/main amd64 libjson-perl all 4.04000-1 [81.8 kB]

Get:2 http://mirrors.tuna.tsinghua.edu.cn/ubuntu jammy/main amd64 libcommon-sense-perl amd64 3.75-2build1 [21.1 kB]

Get:3 http://mirrors.tuna.tsinghua.edu.cn/ubuntu jammy/main amd64 libtypes-serialiser-perl all 1.01-1 [11.6 kB]

Get:4 http://mirrors.tuna.tsinghua.edu.cn/ubuntu jammy/main amd64 libjson-xs-perl amd64 4.030-1build3 [87.2 kB]

Get:5 http://mirrors.tuna.tsinghua.edu.cn/ubuntu jammy-updates/main amd64 sysstat amd64 12.5.2-2ubuntu0.2 [487 kB]

Get:6 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-common all 257.pgdg22.04+1 [94.3 kB]

Get:7 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-common all 257.pgdg22.04+1 [239 kB]

Get:8 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 libpq5 amd64 16.2-1.pgdg22.04+1 [214 kB]

Get:9 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-16 amd64 16.2-1.pgdg22.04+1 [1,886 kB]

Get:10 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-16 amd64 16.2-1.pgdg22.04+1 [18.1 MB]

Get:11 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql all 16+257.pgdg22.04+1 [69.2 kB]

Fetched 21.3 MB in 5min 43s (62.1 kB/s)                                        

Preconfiguring packages ...

Selecting previously unselected package libjson-perl.

(Reading database ... 210553 files and directories currently installed.)

Preparing to unpack .../00-libjson-perl_4.04000-1_all.deb ...

Unpacking libjson-perl (4.04000-1) ...

Selecting previously unselected package postgresql-client-common.

Preparing to unpack .../01-postgresql-client-common_257.pgdg22.04+1_all.deb ...

Unpacking postgresql-client-common (257.pgdg22.04+1) ...

Selecting previously unselected package postgresql-common.

Preparing to unpack .../02-postgresql-common_257.pgdg22.04+1_all.deb ...

Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'

Unpacking postgresql-common (257.pgdg22.04+1) ...

Selecting previously unselected package libcommon-sense-perl:amd64.

Preparing to unpack .../03-libcommon-sense-perl_3.75-2build1_amd64.deb ...

Unpacking libcommon-sense-perl:amd64 (3.75-2build1) ...

Selecting previously unselected package libtypes-serialiser-perl.

Preparing to unpack .../04-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 .../05-libjson-xs-perl_4.030-1build3_amd64.deb ...

Unpacking libjson-xs-perl (4.030-1build3) ...

Selecting previously unselected package libpq5:amd64.

Preparing to unpack .../06-libpq5_16.2-1.pgdg22.04+1_amd64.deb ...

Unpacking libpq5:amd64 (16.2-1.pgdg22.04+1) ...

Selecting previously unselected package postgresql-client-16.

Preparing to unpack .../07-postgresql-client-16_16.2-1.pgdg22.04+1_amd64.deb ...

Unpacking postgresql-client-16 (16.2-1.pgdg22.04+1) ...

Selecting previously unselected package postgresql-16.

Preparing to unpack .../08-postgresql-16_16.2-1.pgdg22.04+1_amd64.deb ...

Unpacking postgresql-16 (16.2-1.pgdg22.04+1) ...

Selecting previously unselected package postgresql.

Preparing to unpack .../09-postgresql_16+257.pgdg22.04+1_all.deb ...

Unpacking postgresql (16+257.pgdg22.04+1) ...

Selecting previously unselected package sysstat.

Preparing to unpack .../10-sysstat_12.5.2-2ubuntu0.2_amd64.deb ...

Unpacking sysstat (12.5.2-2ubuntu0.2) ...

Setting up postgresql-client-common (257.pgdg22.04+1) ...

Setting up libpq5:amd64 (16.2-1.pgdg22.04+1) ...

Setting up libcommon-sense-perl:amd64 (3.75-2build1) ...

Setting up libtypes-serialiser-perl (1.01-1) ...

Setting up libjson-perl (4.04000-1) ...

Setting up sysstat (12.5.2-2ubuntu0.2) ...

Creating config file /etc/default/sysstat with new version

update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode

Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-collect.timer → /lib/systemd/system/sysstat-collect.timer.

Created symlink /etc/systemd/system/sysstat.service.wants/sysstat-summary.timer → /lib/systemd/system/sysstat-summary.timer.

Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.

Setting up libjson-xs-perl (4.030-1build3) ...

Setting up postgresql-client-16 (16.2-1.pgdg22.04+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 (257.pgdg22.04+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...

  en_us

Removing obsolete dictionary files:

'/etc/apt/trusted.gpg.d/apt.postgresql.org.gpg' -> '/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg'

Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.

Setting up postgresql-16 (16.2-1.pgdg22.04+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 this locale configuration:

  provider:    libc

  LC_COLLATE:  en_US.UTF-8

  LC_CTYPE:    en_US.UTF-8

  LC_MESSAGES: en_US.UTF-8

  LC_MONETARY: zh_CN.UTF-8

  LC_NUMERIC:  zh_CN.UTF-8

  LC_TIME:     zh_CN.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

Setting up postgresql (16+257.pgdg22.04+1) ...

Processing triggers for man-db (2.10.2-1) ...

-->