摘要：
1.查看本地postgresql映像[root@localhosthome]#Dockerimages|greppostgrestpostgrest0d2531ee3abd4daysago397MB2。初始化图像[root@localhosthome]#dockerrun--namepostgres2-ePOSTGRES_PASSWORD=密码-p5432:5432-vpgda
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