PostgreSQL元命令之connect

原创多米爸比 2019-12-16

3235

前言：PostgreSQL里连接数据库我们比较常见使用psql进行连接，因为PostgreSQL一个实例里有多个数据库，如果我们想连接到其它数据库，除了可以退出再重连也可以直接使用元命令connect进行数据库连接切换，下面详细介绍一下不同参数使用方式：

1. 常见psql连接数据库

$ psql -h127.0.0.1 -Upostgres postgres
psql (12.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=#

使用元命令\conninfo可以查看数据库连接信息，包括主机、端口、数据库名称、数据库用户

2.同一用户切换到其他数据库

test=# \connect test
You are now connected to database "test" as user "postgres".

当前登录用户为postgres，登录用户不变，切换到test数据库

3. 同一库切换登录用户

test=# \connect - test
Password for user test: 
You are now connected to database "test" as user "test".

当前数据库为test，数据库不变，登录用户由postgres切换到test
单独切换用户，需要加 “-”

4.当前实例同时切换数据库和登录用户

test=> \conninfo
You are connected to database "test" as user "test" via socket in "/var/run/postgresql" at port "5432".
test=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=>

当前数据库为test，登录用户为test，切换数据库为postgres，且切换登录用户为postgres

5.切换主机和数据库

$ psql -Upostgres
psql (12.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=# \connect test - 192.168.99.200
You are now connected to database "test" as user "postgres" on host "192.168.99.200" at port "5432".

主机参数前面需要加 “-”

6.切换主机、数据库和登录用户(端口为默认)

$ psql -Upostgres
psql (12.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=# \connect test test 192.168.99.200
Password for user test: 
You are now connected to database "test" as user "test" on host "192.168.99.200" at port "5432".

7.切换主机、数据库和登录用户以及端口

$ psql -Upostgres
psql (12.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

postgres=# \connect postgres test 192.168.99.200 5432
Password for user test: 
You are now connected to database "postgres" as user "test" on host "192.168.99.200" at port "5432".

8. 使用conninfo

$ psql
psql (12.1)
Type "help" for help.

postgres=> \c "hostaddr=192.168.137.11 port=5432 user=repuser password=xxxxxx dbname=postgres"
Password for user repuser: 
You are now connected to database "postgres" as user "repuser".

最新版本13.2,12.6,11.11,10.16,9.6.21,9.5.25支持connection_string参数中使用密码。
下面是版本12.6可以使用password

$ psql
psql (12.6)
Type "help" for help.

postgres=#  \c "hostaddr=192.168.137.11 port=5432 user=repuser password=xxxxxx dbname=postgres"
You are now connected to database "postgres" as user "repuser" on host "192.168.137.11" at port "5432".