《课程笔记2 | PostgreSQL深入浅出》

《课程笔记1 | PostgreSQL深入浅出》-linux安装pg 12（source）

1.安装

--创建目录
[root@single data]# mkdir -p /usr/local/pgsql
[root@single data]# mkdir -p /usr/local/pgsql/pgdata
[root@single data]# mkdir -p /usr/local/pgsql/pglog
[root@single data]# mkdir -p /usr/local/pgsql/pgarch
[root@single data]# 
[root@single data]# cd /usr/local/pgsql && pwd
/usr/local/pgsql
[root@single pgsql]# ll /usr/local/pgsql
total 16
drwxr-xr-x 2 root root 4096 Aug 29 18:12 bin
drwxr-xr-x 6 root root 4096 Aug 29 18:11 include
drwxr-xr-x 4 root root 4096 Aug 29 18:12 lib
drwxr-xr-x 2 root root    6 Aug 29 18:18 pgarch
drwxr-xr-x 2 root root    6 Aug 29 18:18 pgdata
drwxr-xr-x 2 root root    6 Aug 29 18:18 pglog
drwxr-xr-x 8 root root 4096 Aug 29 18:11 share
--创建用户
[root@single pgsql]# useradd -u 2000 postgres
[root@single pgsql]# echo postgres | passwd --stdin postgres
Changing password for user postgres.
passwd: all authentication tokens updated successfully.
[root@single pgsql]# 
[root@single pgsql]# 
[root@single pgsql]# id postgres && ll /home/
uid=2000(postgres) gid=2000(postgres) groups=2000(postgres)
total 4
drwx------   4 ftpuser  ftpuser   240 Aug 26 23:38 ftpuser
drwx------. 15 oracle   oinstall 4096 Mar 22 19:39 liang
drwx------   5 oracle   oinstall  267 Aug 24 00:40 oracle
drwx------   3 postgres postgres   92 Aug 29 18:20 postgres
--解压安装包（在/root下）
[root@single pgsql]# cd ~
[root@single ~]# tar zxf postgresql-12.12.tar.gz -C /usr/local/pgsql
[root@single ~]# ll /usr/local/pgsql
total 16
drwxr-xr-x 2 root root 4096 Aug 29 18:12 bin
drwxr-xr-x 6 root root 4096 Aug 29 18:11 include
drwxr-xr-x 4 root root 4096 Aug 29 18:12 lib
drwxr-xr-x 2 root root    6 Aug 29 18:18 pgarch
drwxr-xr-x 2 root root    6 Aug 29 18:18 pgdata
drwxr-xr-x 2 root root    6 Aug 29 18:18 pglog
drwxrwxrwx 6 1107 1107  323 Aug  9 05:07 postgresql-12.12
drwxr-xr-x 8 root root 4096 Aug 29 18:11 share
[root@single ~]# 

yum install -y libicu-devel zlib-devel readline readline-devel perl-ExtUtils-Embed perl-ExtUtils-MakeMaker pam-devel openssl-devel openldap-devel libxml2-devel libxslt-devel systemd-devel tcl-devel gcc net-tools python python-devel


cd /usr/local/pgsql/postgresql-12.12/ && mv * ..
cd .. && rm -rf postgresql-12.12 && ls

export PREFIX=/usr/local/pgsql
export PGPORT=5432
./configure
–prefix={PREFIX} \ --exec-prefix={PREFIX}
–bindir={PREFIX}/bin \ --sysconfdir={PREFIX}/etc
–libdir={PREFIX}/lib \ --includedir={PREFIX}/include
–datarootdir={PREFIX}/share \ --datadir={PREFIX}/share
–localedir={PREFIX}/share \ --mandir={PREFIX}/share/man
–docdir={PREFIX}/share/doc \ --htmldir={PREFIX}/share/html
\–enable-nls=“zh_CN en_US”
\–with-perl
\–with-python
\–with-tcl
\–with-icu
\–with-openssl
\–with-ldap
\–with-pam
\–with-systemd
\–with-libxml
\–with-libxslt
\–with-readline
\–with-zlib
\–with-pgport=${PGPORT}
\–with-segsize=1
\–with-blocksize=8
\–with-wal-blocksize=8
--编译安装
make world

make install-world
--安装插件
cd /usr/local/pgsql/contrib
make
make install

--配置环境变量
su - postgres
cat >>/home/postgres/.bash_profile<<EOF
export PGHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}
export PGDATA=${PGHOME}/pgdata
export PATH=${PGHOME}/bin:${PATH}
EOF

source ~//.bash_profile && . ~/.bash_profile
chown -R postgres:postgres /usr/local/pgsql

2.初始化

[root@single contrib]# ll -d /usr/local/pgsql/pgdata && ll -l /usr/local/pgsql/pgdata
drwxr-xr-x 2 postgres postgres 6 Aug 29 18:18 /usr/local/pgsql/pgdata
total 0
[root@single contrib]#
su – postgres
initdb -D $PGDATA -W -U postgres

[postgres@single ~]$ initdb -D $PGDATA -W -U postgres
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.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /usr/local/pgsql/pgdata ... initdb: error: could not change permissions of directory "/usr/local/pgsql/pgdata": Operation not permitted
[postgres@single ~]$ initdb -D $PGDATA -W -U postgres
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.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /usr/local/pgsql/pgdata ... 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

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /usr/local/pgsql/pgdata -l logfile start

[postgres@single ~]$
--修改配置文件postgresql.conf
vi /usr/local/pgsql/pgdata/postgresql.conf

listen_addresses = '*'
port = 5432
--修改配置文件pg_hba.conf
vi /usr/local/pgsql/pgdata/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
--启动数据库
pg_ctl -D /usr/local/pgsql/pgdata -l logfile start

[postgres@single pgdata]$ pg_ctl -D /usr/local/pgsql/pgdata -l logfile start
waiting for server to start.... done
server started
[postgres@single pgdata]$

[postgres@single pgdata]$ ps -ef | grep postgres
root      44766   2291  0 18:30 pts/0    00:00:00 su - postgres
postgres  44768  44766  0 18:30 pts/0    00:00:00 -bash
postgres  51886      1  0 18:39 ?        00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/pgdata
postgres  51888  51886  0 18:39 ?        00:00:00 postgres: checkpointer   
postgres  51889  51886  0 18:39 ?        00:00:00 postgres: background writer   
postgres  51890  51886  0 18:39 ?        00:00:00 postgres: walwriter   
postgres  51891  51886  0 18:39 ?        00:00:00 postgres: autovacuum launcher   
postgres  51892  51886  0 18:39 ?        00:00:00 postgres: stats collector   
postgres  51893  51886  0 18:39 ?        00:00:00 postgres: logical replication launcher   
postgres  51918  44768  0 18:39 pts/0    00:00:00 ps -ef
postgres  51919  44768  0 18:39 pts/0    00:00:00 grep --color=auto postgres
[postgres@single pgdata]$ 

[root@single pgsql]# netstat -tpnl |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      51886/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      51886/postgres      
[root@single pgsql]# 
[root@single pgsql]# 

3.查看

[postgres@single pgdata]$ psql -d postgres
psql (12.12)
Type "help" for help.

postgres=# \l --列出所有数据库
                       List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
------+-------+----------+---------+-------+-------------------
(0 rows)

postgres=# 
postgres=# \du --显示所有用户
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# create database test1;
CREATE DATABASE
postgres=# \c test1;
You are now connected to database "test1" as user "postgres".
test1=# \conninfo
You are connected to database "test1" as user "postgres" via socket in "/tmp" at port "5432".
test1=#