ubuntu 18 虛擬機安裝（4）安裝 postgres sql 數據庫

如何查看PostgreSQL的版本
https://blog.csdn.net/lee_vincent1/article/details/138731465
postgres 查看全部數據庫
https://blog.csdn.net/xie__jin__cheng/article/details/138653002
Ubuntu18.04安裝Postgresql與配置
https://blog.csdn.net/b285795298/article/details/130759531

https://www.postgresql.org/download/linux/ubuntu/

https://www.cnblogs.com/xu360/articles/16860260.html
How To Install and Use PostgreSQL on Ubuntu 18.04
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

postgres新增用戶、創建新庫
https://blog.csdn.net/qq_34322136/article/details/135624871
https://www.jb51.net/article/204207.htm

2.設置數據庫時間和時區
(1). 通過命令
PRC為東八區 北京時間 UTC+8
Asia/Shanghai為東八區 上海時間 GMT+8
但是通過這種方式設置時區是session級別的,退出會話會失效

https://blog.csdn.net/qq_45658339/article/details/124431612

查看所有用戶

cat /etc/passwd

查看所有用戶個數

cat /etc/passwd | wc -l
31

sudo apt-get update
sudo apt-get install postgresql -y

The following additional packages will be installed:libpq5 libsensors4 postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat
Suggested packages:lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl openssl-blacklist isag
The following NEW packages will be installed:libpq5 libsensors4 postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common ssl-cert sysstat
0 upgraded, 9 newly installed, 0 to remove and 45 not upgraded.
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 libsensors4:amd64 (1:3.4.0-4ubuntu0.1) ...
Setting up postgresql-client-10 (10.23-0ubuntu0.18.04.2) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up sysstat (11.6.1-1ubuntu0.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/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up postgresql-10 (10.23-0ubuntu0.18.04.2) ...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
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/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... okSuccess. You can now start the database server using:/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile startVer Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (10+190ubuntu0.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for libc-bin (2.27-3ubuntu1.5) ...
Processing triggers for systemd (237-3ubuntu10.57) ...

sudo systemctl status postgresql
sudo systemctl start postgresql
sudo systemctl stop postgresql
：

postgresql.service - PostgreSQL RDBMSLoaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)Active: active (exited) since Thu 2024-06-27 14:29:57 CST; 10min agoMain PID: 2387 (code=exited, status=0/SUCCESS)Tasks: 0 (limit: 4623)CGroup: /system.slice/postgresql.serviceJun 27 14:29:57 jack systemd[1]: Starting PostgreSQL RDBMS...
Jun 27 14:29:57 jack systemd[1]: Started PostgreSQL RDBMS.

psql --version

psql (PostgreSQL) 10.23 (Ubuntu 10.23-0ubuntu0.18.04.2)

在Ubuntu中安裝Postgresql之后，會自動注冊服務，并隨操作系統自動啟動。
在Ubuntu中安裝Postgresql之后，會自動添加一個postgresql的操作系統的用戶，密碼是隨機的。并且會自動生成一個名字為postgresql的數據庫，用戶名也是postgresql，密碼也是隨機的。
用postgres用戶登錄：

sudo -u postgres psql

或者是

sudo su - postgres
psql

修改PostgresSQL數據庫配置

vi /etc/postgresql/10/main/postgresql.conf

1.1 修改配置文件

vi /etc/postgresql/10/main/postgresql.conf

1.監聽任何地址訪問，修改連接權限

#listen_addresses = 'localhost' 改為 
listen_addresses = '*'

2.啟用密碼驗證

#password_encryption = md5		# md5 or scram-sha-256
password_encryption = md5		# md5 or scram-sha-256

#shared_preload_libraries = ''		# (change requires restart)
shared_preload_libraries = 'timescaledb'		# (change requires restart)

4. 時區這個不確定 要不要修改

log_timezone = 'Asia/Shanghai'
log_timezone = 'PRC'

5. 時區不確定要不要修改

timezone = 'Asia/Shanghai'
timezone = 'PRC'

sudo vi /etc/postgresql/10/main/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5

Ubuntu的默認軟件包存儲庫中沒有TimescaleDB，因此在此步驟中，您將從TimescaleDB Personal Packages Archive(PPA)安裝它。

First, add Timescale’s APT repository:

首先，添加Timescale的APT存儲庫：

sudo add-apt-repository ppa:timescale/timescaledb-ppa

Confirm this action by hitting the ENTER key.

通過按ENTER鍵確認此操作。

Next, refresh your APT cache to update your package lists:

接下來，刷新您的APT緩存以更新您的軟件包列表：

sudo apt update

sudo apt install timescaledb-postgresql-10

現在已安裝TimescaleDB并準備使用它。 接下來，您將打開它并在PostgreSQL配置文件中調整與它相關聯的一些設置以優化數據庫。

添加用戶

執行這句話的時候，一定要注意查看，當前是否是測試環境
drop role jack ;
create user jack with password '12345' superuser;
select * from pg_user; 

111

https://blog.csdn.net/ctypyb2002/article/details/79881745

pg_dump -h 127.0.0.1 -U jack-d abc -f abc_bak_2024_06_28.sql

out

Password: 
pg_dump: NOTICE: there are circular foreign-key constraints on this table:
pg_dump:   hypertable
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
pg_dump: NOTICE: there are circular foreign-key constraints on this table:
pg_dump:   chunk
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.