初识PostgreSQL

[root@postgres local]# tar -xzvf postgresql-9.6.14-2-linux-x64-binaries.tar.gz 
[root@postgres local]# ls -lrt
total 119548
drwxr-xr-x. 2 root root         6 Nov  5  2016 src
drwxr-xr-x. 2 root root         6 Nov  5  2016 sbin
drwxr-xr-x. 2 root root         6 Nov  5  2016 libexec
drwxr-xr-x. 2 root root         6 Nov  5  2016 lib64
drwxr-xr-x. 2 root root         6 Nov  5  2016 lib
drwxr-xr-x. 2 root root         6 Nov  5  2016 include
drwxr-xr-x. 2 root root         6 Nov  5  2016 games
drwxr-xr-x. 2 root root         6 Nov  5  2016 etc
drwxr-xr-x. 2 root root         6 Nov  5  2016 bin
drwxr-xr-x. 5 root root        49 Jun  9 08:23 share
drwxrwxr-x  9 root root       104 Jun 25 20:45 pgsql
-rw-r--r--  1 root root 122416210 Jul 27 12:56 postgresql-9.6.14-2-linux-x64-binaries.tar.gz

[root@postgres local]# useradd postgres
[root@postgres local]# passwd postgres
Changing password for user postgres.
New password: 
BAD PASSWORD: The password fails the dictionary check - it is too simplistic/systematic
Retype new password: 
passwd: all authentication tokens updated successfully.

[root@postgres local]# mkdir -p data/pg_data
[root@postgres local]# chown postgres:postgres -R data
[root@postgres local]# chown postgres:postgres -R pgsql/

--PostgreSQL不允许使用root进行初始化
[root@postgres bin]# ./initdb -D data/pg_data/
initdb: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
--切换至postgres用户进行初始化
[postgres@postgres ~]$ usr/local/pgsql/bin/initdb -D data/pg_data/
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 data/pg_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


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:


    usr/local/pgsql/bin/pg_ctl -D data/pg_data/ -l logfile start

[postgres@postgres ~]$ cat data/pg_data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
logging_collector=on
--此时添加logging_collector参数目的是让日志输出到指定目录中，其它作用下面再讨论。

[postgres@postgres ~]$ usr/local/pgsql/bin/pg_ctl -D data/pg_data/ start  
server starting
[postgres@postgres ~]$ LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".


日志输出如下：
LOG:  database system was shut down at 2019-07-27 13:07:07 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@postgres ~]$ usr/local/pgsql/bin/pg_ctl -D data/pg_data/ stop
waiting for server to shut down.... done
server stopped


日志输出如下：
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down

[root@postgres soft]# bzip2 -d postgresql-9.6.14.tar.bz2 
[root@postgres soft]# tar -xvf postgresql-9.6.14.tar 
[root@postgres soft]# ls -lrt 
total 117560
drwxrwxrwx 6 root root       273 Jun 18 05:35 postgresql-9.6.14
-rw-r--r-- 1 root root 120381440 Jul 27 12:55 postgresql-9.6.14.tar

[root@postgres soft]# cd postgresql-9.6.14
[root@postgres postgresql-9.6.14]# ./configure 
--这里可能会有缺包报错，一般按照提示安装需求的包即可
--实验环境，约30秒执行完成

[root@postgres postgresql-9.6.14]# make
.....(输出省略，实验环境约需3分钟)
最后输出，代表make完成：
All of PostgreSQL successfully made. Ready to install.


[root@postgres postgresql-9.6.14]# make install
....（输出省略）
PostgreSQL installation complete.

(1)createdb 命令创建
[postgres@postgres bin]$ ./createdb test1
(2)命令行模式下创建
postgres=# create database test2;
CREATE DATABASE

test1=# create table t1 (id int,name char(40),constraint t1_pk primary key(id));  
CREATE TABLE
test1=# insert into t1 values (1,'张三');
INSERT 0 1
test1=# select * from t1;
 id |                    name                    
----+--------------------------------------------
  1 | 张三                                      
(1 row)

test1=# create user cg with login password '123456';
CREATE ROLE
test1=# alter database test2 owner to cg;
ALTER DATABASE
[postgres@postgres bin]$ ./psql -U cg -d test2
psql (9.6.14)
Type "help" for help.


test2=> select user;
 current_user 
--------------
 cg
(1 row)
test2=> create table t2 (id int,name char(30),constraint t2_pk primary key(id));
CREATE TABLE
test2=> insert into t2 values (1,'李四');
INSERT 0 1
test2=> select * from t2;
 id |               name               
----+----------------------------------
  1 | 李四                            
(1 row)

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
 test1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test2     | cg       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)


postgres=# \l test2
                            List of databases
 Name  | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-------+-------+----------+-------------+-------------+-------------------
 test2 | cg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

postgres=# \c test1
You are now connected to database "test1" as user "postgres".
test1=# \c postgres
You are now connected to database "postgres" as user "postgres".

test1=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
(1 row)
test1=# \d t1
         Table "public.t1"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 id     | integer       | not null
 name   | character(40) | 
Indexes:
    "t1_pk" PRIMARY KEY, btree (id)

test1=# select * from t2;
 id |                 name                  | age 
----+---------------------------------------+-----
  1 | 张三                                  |  19
  2 | 李四                                  |  33
  3 | 王五                                  |  15
(3 rows)


test1=# \x
Expanded display is on.


test1=# select * from t2;                   
-[ RECORD 1 ]-------------------------------
id   | 1
name | 张三                                 
age  | 19
-[ RECORD 2 ]-------------------------------
id   | 2
name | 李四                                 
age  | 33
-[ RECORD 3 ]-------------------------------
id   | 3
name | 王五                                 
age  | 15

test1=# select * from t2;
 id |                 name                  | age 
----+---------------------------------------+-----
  1 | 张三                                  |  19
  2 | 李四                                  |  33
  3 | 王五                                  |  15
(3 rows)


test1=# \t
Tuples only is on.


test1=# select * from t2;
  1 | 张三                                  |  19
  2 | 李四                                  |  33
  3 | 王五                                  |  15

[postgres@postgres bin]$ ./psql
psql (9.6.14)
Type "help" for help.


postgres=# \q
[postgres@postgres bin]$