PostgreSQL安装

1
2
3
4

# CentOS
yum install -y gcc make readline-devel openssl-devel zlib-devel systemd-devel uuid-devel
# Ubuntu
apt install -y gcc make libreadline6-dev libssl-dev zlib1g-dev libpam0g-dev libsystemd-dev uuid-dev

1
2
3
4

# wget https://ftp.postgresql.org/pub/source/v13.0/postgresql-13.0.tar.gz
wget https://mirror.tuna.tsinghua.edu.cn/postgresql/source/v14.10/postgresql-14.10.tar.gz
tar -zxf postgresql-14.10.tar.gz
cd postgresql-14.10

1
2
3
4
5
6
7
8

./configure \
--prefix=/usr/local/pgsql \
--with-openssl \
--with-pam \
--with-systemd \
--with-uuid=e2fs

make && make install

1

select * from pg_available_extensions;

1

CREATE EXTENSION pgcrypto;

1

select * from gen_random_uuid()

1
2
3
4
5

useradd postgres -m -s /bin/bash
passwd postgres
mkdir -p /data/pgsql/{data,log}
touch /data/pgsql/log/pgsql.log
chown -R postgres:postgres /data/pgsql

1
2
3
4
5
6
7
8

vi /etc/profile

# set postgresql environment
export PGHOME=/data/pgsql
export PGBASE=/usr/local/pgsql
export PATH=$PATH:$PGBASE/bin

source /etc/profile

1
2
3

切换到postgres执行
su - postgres
initdb -D /data/pgsql/data

1

vi /data/pgsql/data/pg_hba.conf

1
2
3
4
5

listen_addresses = '*'
log_destination = 'csvlog'
logging_collector = on
log_rotation_age = 1d
log_truncate_on_rotation = on

 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

cat > /usr/lib/systemd/system/postgresql.service << 'EOF'
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking
 
User=postgres
Group=postgres
 
# Port number for server to listen on
#Environment=PGPORT=5432
 
# Location of database directory
Environment=PGDATA=/data/pgsql/data
 
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
 
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
 
#ExecStartPre=/usr/local/pgsql/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} 
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} 
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA}
 
# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300
 
[Install]
WantedBy=multi-user.target
EOF

1

systemctl enable postgresql.service --now

1
2
3
4
5
6
7
8

postgres@server:~$ psql
psql (14.10)
Type "help" for help.

postgres=# \password
Enter new password for user "postgres": 
Enter it again: 
postgres=# \q

1
2
3
4
5
6
7
8
9

docker run -d \
    --name postgres \
    -e POSTGRES_DB=db \
    -e POSTGRES_USER=postgres
    -e POSTGRES_PASSWORD=postgres \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -e TZ=PRC
    -v /data/pgdata:/var/lib/postgresql/data \
    postgres