PostgreSQL在安装时自带了pgAdmin
和psql
,pgAdmin
是可视化工具,psql
是命令行工具。
虽然pgAdmin操作起来会更加直观简单,但是在restore和backup db的时候,效率和性能会比较低下,如果db过于庞大,还会导致pgAdmin内存溢出。
推荐使用psql来连接数据库进行备份和恢复db,同样大小的db,使用psql来restore会比pgAdmin快上数倍!
1 | psql -h <dbserver_IP> -p<dbserver_port> -d <database_Name> -U <db user> |
---|
如果host是localhost,可以不指定该参数,当不指定端口号时会使用默认的端口号5432
,或者你可以通过-p
来指定其他端口号。
比如你想连接本地的db:test:5432
,用户名是postgres
,可以使用如下的命令:
1 | psql -d test -U postgres |
---|
如果有密码的话会提示你输入密码,连接数据库后就可以直接通过sql语句来进行相关的操作了。
psql命令也可以不指定某个数据库,如下:
1 | psql -h <dbserver_IP> -p<dbserver_port> -U <db user> |
---|
这时候登陆成功后会进入psql的命令台,此时可以跑一些数据库备份、创建数据库或者连接数据库之类的操作。
在psql的命令台输入\c <database_Name>
,接着按下回车键,即可连接到对应的数据库,如下:
1 2 3 4 5 6 | postgres=# \c cbx6_dev WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. You are now connected to database "cbx6_dev" as user "postgres". cbx6_dev=# |
---|
如果已经连接到一个数据库了,这时候想切换到另一个数据库怎么办?很简单,还是跑这个\c
的命令即可。
和其他的命令行工具不一样,psql在退出时并不是使用exit
,而是使用\q
,接着按下回车就行了,这里的q指的就是quit。
如下:
1 2 3 4 5 | #linux export PGPASSWORD=12345 #windows set PGPASSWORD=12345 |
---|
.pgpass
在~/
目录下创建隐藏文件.pgpass
,~/
目录也就是当前用户的用户目录。在密码文件中填写hostname:port:database:username:password
的内容,如下:
1 2 3 | localhost:5432:db1:admin:admin localhost:5432:db2:admin:admin localhost:5432:db3:admin:admin |
---|
然后需要配置一个环境变量PGPASSFILE
,指向.pgpass
密码文件的路径:
1 | set PGPASSFILE=C:\Users\xxx\.pgpass |
---|
这种方法的好处是可以同时定义多个不同主机上的数据库账号密码。
pg_hba.conf
打开PostgreSQL安装目录下的\data\pg_hba.conf
,将其中的连接对应的md5
改为trust
,然后重启服务。如下:
1 2 3 4 5 6 | # TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all 127.0.0.1/32 md5 <= 改为trust host all all 172.20.1.0/24 md5 host all all 172.168.199.0/24 md5 |
---|
首先通过psql连接到对应的db:
1 | psql -d db1 -U userA |
---|
接着输入密码,进入数据库后,输入:
1 | \i /pathA/xxx.sql |
---|
这里有个问题,如果你把SQL文件的路径里的路径分隔符写成了\
,会报错说Permission denied
。
这里的文件路径必须使用Linux平台下的路径分隔符/
,否则会报错。
这种方式无需先登录数据库,直接用一个命令就可以了:
1 | psql -d db1 -U userA -f /pathA/xxx.sql |
---|
接着输入密码即可执行SQL文件到对应的db里。
小技巧:可以直接把sql文件拖到cmd窗口里,会自动把该sql文件的所在路径给输入到命令行中。
格式如下:
1 | pg_dump -h <host> -p <port> -U <db_user> -F c -f <dump_path> <db_name> |
---|
demo:
1 | pg_dump -h localhost -p 5432 -U postgres -F c -f D:\dump_jar\test.dump test |
---|
1 | pg_restore -h <host> -p <port> -U <db_user> -W -O -d <db_name> <dump_path> |
---|
demo:
1 | pg_restore -h localhost -p 5432 -U postgres -W -O -d test test.DUMP |
---|
这里的db要先创建好,dump本质上就是一大堆sql,可以通过该命令导入到已存在的db里。另外,执行pg_restore时需要输入密码。
警告
本文最后更新于 February 3, 2021,文中内容可能已过时,请谨慎使用。