docker运行postgresql

启动，挂载数据目录

docker run --name postgis_dev -e POSTGRES_PASSWORD=123456 -v /Users/cardiac/Docker/pg_data01:/var/lib/postgresql/data -p 5432:5432 -d mdillon/postgis

注： -v: 前面的路径是本地系统的路径，冒号":" 后面的路径是postgresql运行的路径，配置文件可以放在本地路径下，并且可以更改配置文件中的参数。

进入bash命令行

docker exec -it postgis_dev bash

关闭容器

docker stop postgis_dev

启动容器

docker start postgis_dev

 

使用psql查看数据库，注意：需要切换一下用户。

root@e2452813de70:/# psql -l psql: FATAL: role "root" does not exist 
root@e2452813de70:/# su postgres 
postgres@e2452813de70:/$ psql -l 
List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges
------------------+----------+----------+------------+------------+-----------------------
 mydb | 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 
template_postgis | postgres | UTF8 | en_US.utf8 | en_US.utf8 | 
vicky | postgres | UTF8 | en_US.utf8 | en_US.utf8 | 
(6 rows)

 

命令行登录postgresql数据库

root@e2452813de70:/# psql -U postgres -W 
Password: 
psql (11.2 (Debian 11.2-1.pgdg90+1)) 
Type "help" for help.

 

-U 指定用户

-W 使用密码登录

 

psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

举例： psql -h 192.168.1.121 -p 5432 vicky postgres

 

登录数据库后，

列出所有数据库： \list

postgres=# \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------------------+----------+----------+------------+------------+----------------------- mydb | 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 template_postgis | postgres | UTF8 | en_US.utf8 | en_US.utf8 | vicky | postgres | UTF8 | en_US.utf8 | en_US.utf8 | (6 rows)

 

切换数据库： \c 数据库名称

postgres=# \c vicky

 

查看所有表 \d （注意：在docker里面启动的环境，使用\d会报 Did not find any relations. 并没有列出所有表）

vicky=# \d Did not find any relations.

 

查看对象结构，对象可以是表，索引，视图等

\d (表名,索引名称，视图)

vicky=# \d ods.my_concubine Table "ods.my_concubine" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id | integer | | | name | character varying(50) | | | age | integer | | |

 

查看对象的详细信息： \d+ (表名,索引名称，视图)

vicky=# \d+ ods.my_concubine

Table "ods.my_concubine"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------+-----------+----------+---------+----------+--------------+-------------

id | integer | | | | plain | |

name | character varying(50) | | | | extended | |

age | integer | | | | plain | |

列出所有的schema

vicky=# \dn

List of schemas Name | Owner

--------+----------

ods | postgres

public | postgres

(2 rows)

 

列出所有的表空间

vicky=# \db List of tablespaces Name | Owner | Location

------------+----------+----------

pg_default | postgres |

pg_global | postgres | (2 rows)

 

列出数据库的所有角色或用户

vicky=# \dg

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

vicky=# \du

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

列出表的权限分配

vicky=# \dp ods.my_concubine

Access privileges

Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+-------------------+-------------------+----------

ods | my_concubine | table | | |

(1 row)