【docker-compose】一键安装PostgreSQL数据库

[root@docker ~]# mkdir PostgreSQL
[root@docker ~]# cd PostgreSQL/

wget https://raw.githubusercontent.com/colovu/docker-postgres/master/docker-compose.yml

[root@docker PostgreSQL]# ls
data  docker-compose.yml
[root@docker PostgreSQL]# vim docker-compose.yml 
[root@docker PostgreSQL]# cat docker-compose.yml 
version: "3.3"
services:
 postgres:
  image: postgres:12-alpine
  container_name: xybdiy_postgres
  restart: always
  environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
  ports:
    - 5432:5432
  volumes:
    - /root/PostgreSQL/data:/var/lib/postgresql/data

docker-compose up -d

[root@docker PostgreSQL]# docker-compose up -d
Pulling postgres (postgres:12-alpine)...
12-alpine: Pulling from library/postgres
59bf1c3509f3: Already exists
c50e01d57241: Pull complete
a0646b0f1ead: Pull complete
45cbd5b44a2d: Pull complete
902601345251: Pull complete
ab80db796fc0: Pull complete
1d825ba7fe16: Pull complete
91db3a1e8d17: Pull complete
Digest: sha256:0b634f9f533f9ce0411148ce9571ece8bed9475aa74fb3620bac60bf7a521d70
Status: Downloaded newer image for postgres:12-alpine
Creating xybdiy_postgres ... done
[root@docker PostgreSQL]#

[root@docker PostgreSQL]# docker-compose ps
     Name                    Command              State                    Ports                  
--------------------------------------------------------------------------------------------------
xybdiy_postgres   docker-entrypoint.sh postgres   Up      0.0.0.0:5432->5432/tcp,:::5432->5432/tcp
[root@docker PostgreSQL]# docker ps
CONTAINER ID   IMAGE                COMMAND                  CREATED             STATUS             PORTS                                       NAMES
a5a952cf662e   postgres:12-alpine   "docker-entrypoint.s…"   2 minutes ago       Up 2 minutes       0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   xybdiy_postgres
74dde54efbe0   wordpress:latest     "docker-entrypoint.s…"   About an hour ago   Up About an hour   0.0.0.0:8000->80/tcp, :::8000->80/tcp       wordpress_wordpress_1
f22bc03ae9ca   mysql:5.7            "docker-entrypoint.s…"   About an hour ago   Up About an hour   3306/tcp, 33060/tcp                         wordpress_db_1

[root@docker PostgreSQL]# docker exec -it a5a952cf662e bash
bash-5.1# psql -U root -W
Password: 
psql (12.9)
Type "help" for help.

root=# 

# 查询当前时间
root=# select now();
              now              
-------------------------------
 2022-04-23 16:42:10.781947+00
(1 row)

#查询亚洲/上海地区时间
root=# select  now() at time zone 'Asia/Shanghai';
          timezone          
----------------------------
 2022-04-24 00:42:20.457047
(1 row)

# 设置postgres数据库的时区
root=# ALTER DATABASE "postgres" SET timezone TO 'Asia/Shanghai';
ALTER DATABASE

# 使用帮助命令
root=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

# 创建数据库xybdiy
root=# CREATE DATABASE xybdiy;
CREATE DATABASE

# 查看已存在的数据库
root=# \list
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
 root      | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
 template1 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
 xybdiy    | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
(5 rows)

# 进入数据库xybdiy
root=# \connect xybdiy
Password: 
You are now connected to database "xybdiy" as user "root".
xybdiy=# 

# 创建表格
xybdiy=# CREATE TABLE COMPANY(
xybdiy(#    ID INT PRIMARY KEY     NOT NULL,
xybdiy(#    NAME           TEXT    NOT NULL,
xybdiy(#    AGE            INT     NOT NULL,
xybdiy(#    ADDRESS        CHAR(50),
xybdiy(#    SALARY         REAL
xybdiy(# );
CREATE TABLE
xybdiy=# 

# 查看表格
xybdiy=# \display
                List of relations
 Schema |     Name     | Type  | Owner |  Table  
--------+--------------+-------+-------+---------
 public | company_pkey | index | root  | company
(1 row)

# 查看表格信息
xybdiy=# \d company
                  Table "public.company"
 Column  |     Type      | Collation | Nullable | Default 
---------+---------------+-----------+----------+---------
 id      | integer       |           | not null | 
 name    | text          |           | not null | 
 age     | integer       |           | not null | 
 address | character(50) |           |          | 
 salary  | real          |           |          | 
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

xybdiy=# 

# 删除表格
\xybdiy=# drop table company,department;
DROP TABLE
xybdiy=# \d
Did not find any relations.

# 删除数据库
root=# drop database xybdiy;
DROP DATABASE
root=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
 root      | root  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
 template1 | root  | UTF8     | en_US.utf8 | en_US.utf8 | =c/root          +
           |       |          |            |            | root=CTc/root
(4 rows)

root=#