docker安装的postgresql的基本使用

1、查看本地postgresql镜像

[root@localhost home]# docker images |grep postgres
postgres                    latest              0d2531ee3abd        4 days ago          397MB

2、初始化镜像

[root@localhost home]# docker run --name postgres2 -e POSTGRES_PASSWORD=password -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres
72c5af7ed83cf389d8a950411dd25124d0feae1b017213818d1b19d2609c7a49

-p端口映射

-v将数据存到宿主服务器

-e POSTGRES_PASSWORD 密码（默认用户名postgres）

-e TZ=PRC时区，中国

-d后台运行

--name容器名称

时区问题

如果在启动容器时不设置时区，默认为UTC，使用now()设置默认值的时候将有时间差。

3、进入镜像

[root@localhost home]# docker exec -it postgres2 /bin/bash
root@72c5af7ed83c:/# ls
bin  boot  dev  docker-entrypoint-initdb.d  docker-entrypoint.sh  etc  home  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var

镜像的data目录在 /var/lib/postgresql/data

root@72c5af7ed83c:/# cd /var/lib/postgresql/data
root@72c5af7ed83c:/var/lib/postgresql/data# ls
base          pg_dynshmem    pg_logical    pg_replslot   pg_stat      pg_tblspc    pg_wal                postgresql.conf
global        pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf  postmaster.pid

4、进入postgresql的工具目录 /usr/lib/postgresql/12/bin

root@72c5af7ed83c:/# cd /usr/lib/postgresql/12/bin
root@72c5af7ed83c:/usr/lib/postgresql/12/bin# ls
clusterdb   dropdb    oid2name           pgbench       pg_controldata  pg_dumpall     pg_recvlogical  pg_rewind      pg_test_timing  postgres    reindexdb
createdb    dropuser  pg_archivecleanup  pg_checksums  pg_ctl          pg_isready     pg_resetwal     pg_standby     pg_upgrade      postmaster  vacuumdb
createuser  initdb    pg_basebackup      pg_config     pg_dump         pg_receivewal  pg_restore      pg_test_fsync  pg_waldump      psql        vacuumlo

5、连接数据库

root@72c5af7ed83c:/usr/lib/postgresql/12/bin# psql -Upostgres
psql (12.2 (Debian 12.2-1.pgdg100+1))
Type "help" for help.

postgres=#

6、创建数据库db1，在db1中插入对象，备份db1的数据，还原到db2

--创建数据库
postgres=# CREATE DATABASE DB1;
CREATE DATABASE
postgres=# CREATE DATABASE DB2;
CREATE DATABASE
--查看数据库

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 db1       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 db2       | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(5 rows)
--切换数据库
postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# create table t1(a int);
CREATE TABLE
db1=# insert into t1 values (123);
INSERT 0 1
db1=# select * from t1;
  a
-----
 123
(1 row)

备份

./pg_dump -Upostgres -ddb1 -F c -f dump.sql

还原

./pg_restore -Upostgres -ddb2 -Fc  dump.dmp

查看还原结果

root@72c5af7ed83c:/usr/lib/postgresql/12/bin# psql -Upostgres -d db2
psql (12.2 (Debian 12.2-1.pgdg100+1))
Type "help" for help.

db2=# select * from t1;
  a
-----
 123
(1 row)

---end