pgAdmin

psql使用
-bash-4.2$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
psql [OPTION]… [DBNAME [USERNAME]]

General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: “postgres”)
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 (“one”), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
–help=commands list backslash commands, then exit
–help=variables list special variables, then exit

Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)

Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: “|”)
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte

Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: “local socket”)
-p, --port=PORT database server port (default: “5432”)
-U, --username=USERNAME database user name (default: “postgres”)
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)

For more information, type “?” (for internal commands) or “\help” (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.

psql使用
psql –h hostname –p 5432 –d dbname –U username –W

[root@node201 ~]# su - postgres
Last login: Sat Apr 3 10:13:03 CST 2021 on pts/1
-bash-4.2$ psql -h localhost -p5432 -dtest
psql: FATAL: Ident authentication failed for user “postgres”
-bash-4.2$

编辑文件/etc/postgresql/8.4/main/pg_hba.conf并用md5或trust替换ident或peer，具体取决于您是否希望它在您自己的计算机上要求输入密码。
解决办法：
[root@node201 ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf #将下面三行注释取消，同时将ident和peer改成trust

replication privilege.

local replication postgres trust
host replication postgres 127.0.0.1/32 trust
host replication postgres ::1/128 trust

改完后重启，还是不能登录，查看日志如下：
[root@node201 ~]# tail /var/lib/pgsql/9.6/data/pg_log/postgresql-Sat.log -f
…
< 2021-04-03 11:06:13.846 CST > LOG: could not connect to Ident server at address “::1”, port 113: Connection refused
< 2021-04-03 11:06:13.846 CST > FATAL: Ident authentication failed for user “postgres”
< 2021-04-03 11:06:13.846 CST > DETAIL: Connection matched pg_hba.conf line 84: “host all all ::1/128 ident”

解决办法 ：
修改84行内容为trust：

IPv6 local connections:

host all all ::1/128 trust

重启重新登录：
[root@node201 ~]# systemctl restart postgresql-9.6

-bash-4.2$ psql -h localhost -p5432 -dtest
psql (9.6.21)
Type “help” for help.

查询当前时间：
$ psql -c “SELECT current_time”
-bash-4.2$ psql -h localhost -p5432 -dtest -c “select current_time” #用这种方式可以去执行一些批处理
timetz

11:22:11.273859+08
(1 row)

执行批外理：
-bash-4.2$ cat test.sql
select current_time

-bash-4.2$ psql -h localhost -p5432 -dtest -f test.sql
timetz

11:25:54.569173+08
(1 row)

查看帮助
postgres=# help #查看帮助

postgres=# \h DELETE #查看delete的用法

test=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, …] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING from_item [, …] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ]

psql -d postgresql_book -c “DROP TABLE IF EXISTS dross; CREATE SCHEMA staging;”

postgres=# \timing on #开启计时
Timing is on.
postgres=# select count(*) from pg_tables; #执行命令，查看命令执行时间
count

62

(1 row)

Time: 22.846 ms

postgres=# \set autocommit off #关闭自动提交

postgres=# create table test(id int); #创建一个表
CREATE TABLE
Time: 27.924 ms
postgres=# insert into test values(1); #插入值
INSERT 0 1
Time: 2.355 ms
postgres=# rollback;
WARNING: there is no transaction in progress
ROLLBACK
Time: 11.426 ms
postgres=# select * from test;
id

1
(1 row)

Time: 0.970 ms

#好像失败了，原因是上面的\set autocommit off中的autocommit必须大写；

UPDATE census.facts SET short_name = ‘this is a mistake’;
ROLLBACK;
COMMIT;

设置变量
\set eav ‘EXPLAIN ANALYZE VERBOSE’ #设置变量eav；
:eav SELECT COUNT(*) FROM pg_tables;

设置历史记录
\set HISTSIZE 10

\set HISTFILE ~/.psql_history- :HOST - :DBNAME

\l 可以显示当前数据库服务器中所有的数据库名
\dt 列出当前数据库中数据表
\du (查看用户)
\dt: 查看所有自己创建的表
\dt+: 查看所有自己创建的表，显示表的相关内容占的磁盘大小

\dt+ pg_catalog.pg_t*
postgres=# \dt+ pg_catalog.pg_t*
List of relations
Schema | Name | Type | Owner | Size | Description
------------±-----------------±------±---------±-----------±------------
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_transform | table | postgres | 0 bytes |
pg_catalog | pg_trigger | table | postgres | 8192 bytes |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 104 kB |
(9 rows)

postgres=# \dt *.
List of relations
Schema | Name | Type | Owner
--------------------±------------------------±------±---------
information_schema | sql_features | table | postgres
information_schema | sql_implementation_info | table | postgres
information_schema | sql_languages | table | postgres
information_schema | sql_packages | table | postgres
information_schema | sql_parts | table | postgres
information_schema | sql_sizing | table | postgres
information_schema | sql_sizing_profiles | table | postgres
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
…
pg_catalog | pg_user_mapping | table | postgres
public | test | table | postgres
(63 rows)

postgres=# \dt test
List of relations
Schema | Name | Type | Owner
--------±-----±------±---------
public | test | table | postgres
(1 row)

\d+ pg_ts_dict

将数据库拷贝出来：
postgres=# copy test to ‘/tmp/test.txt’;
COPY 3
Time: 0.742 ms

#查看
[root@node201 ~]# ls -al /tmp/test.txt
-rw-r–r-- 1 postgres postgres 6 Apr 3 12:18 /tmp/test.txt
[root@node201 ~]# cat /tmp/test.txt
1
3
4

rpm安装

https://www.pgadmin.org/download/pgadmin-4-rpm/

or on Redhat or CentOS, run:
sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm

To install pgAdmin, run one of the following commands:

Install for both desktop and web modes.

sudo yum install pgadmin4 #同时安装desktop和web模式了

Install for desktop mode only.

sudo yum install pgadmin4-desktop

Install for web mode only.

sudo yum install pgadmin4-web

查看可安装版本
[root@localhost ~]# yum search pgadmin

Finally, if you have installed pgadmin4 or pgadmin4-web, run the web setup script to configure the system to run in web mode:
sudo /usr/pgadmin4/bin/setup-web.sh

报错：
[root@node201 ~]# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform…
Creating configuration database…
Traceback (most recent call last):
File “/usr/pgadmin4/web/setup.py”, line 499, in
setup_db()
File “/usr/pgadmin4/web/setup.py”, line 371, in setup_db
app = create_app()
File “/usr/pgadmin4/web/pgadmin/init.py”, line 431, in create_app
driver.init_app(app)
File “/usr/pgadmin4/web/pgadmin/utils/driver/init.py”, line 40, in init_app
DriverRegistry.load_drivers()
import_module(module_name)
File “/usr/lib64/python3.6/importlib/init.py”, line 126, in import_module
return _bootstrap._gcd_import(name[level:], package, level)
File “

用一个干净的糸统，用官方文档上面的操作方法即可安装成功！

----------------跳过-------
设置pip清华源：
[root@node202 data]# pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
或者是阿里的源，建议使用阿里的
[root@node202 data]# pip config set global.index-url https://mirrors.aliyun.com/pypi/simple

[root@node202 data]# pip install -U torchvision==0.4.0

用干净的操作糸统安装后启动服务：
[root@node160 ~]# /usr/pgadmin4/bin/setup-web.sh
Setting up pgAdmin 4 in web mode on a Redhat based platform…
Creating configuration database…
NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: 9499475@qq.com #设置用户名和密码
Password:
Retype password:
pgAdmin 4 - Application Initialisation

Creating storage and log directories…
Configuring SELinux…
setsebool: SELinux is disabled.
setsebool: SELinux is disabled.
/usr/pgadmin4/bin/setup-web.sh: line 87: semanage: command not found
/usr/pgadmin4/bin/setup-web.sh: line 89: semanage: command not found
The Apache web server is not running. We can enable and start the web server for you to finish pgAdmin 4 installation. Continue (y/n)? y
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.
Apache successfully enabled.
Apache successfully started.
You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
上面有报错，安装包：
[root@node160 ~]# yum install policycoreutils-python

访问
http://192.168.8.160/pgadmin4

修改一下pg的监听地址：
[root@node201 ~]# vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = ‘192.168.8.201’ #IP地址必须加双引号，否则会报错

[root@node201 ~]# systemctl restart postgresql-9.6.service
[root@node201 ~]# netstat -tunlp|grep 5432
tcp 0 0 192.168.8.201:5432 0.0.0.0:* LISTEN 34869/postmaster

连接：

设置一个名字后，再配置一下连接信息：

点save报SSL错误，关闭SSL

还是报错：

添加权限：
[root@node201 ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf

“local” is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 127.0.0.1/32 ident

IPv6 local connections:

host all all ::1/128 trust
host all postgres 192.168.8.0/24 trust

重启：
[root@node201 ~]# systemctl restart postgresql-9.6.service

继续：

安装一个windows的也非常方便：

这次使用SSL进行连接，名称为top：

还可以创建用户：

还可以创建database：

还可以看到sql语句：

查询（不能跨库查询哦）：

跨库查询失败，因为test库中没有test这个表，test表只有在postgres库中才有：

去postgres库中去查询这个表（用户名地址别弄错了）：

创建一个表：

源码安装参考：https://zhuanlan.zhihu.com/p/98434824

(二) pgAdmin编译安装
1、下载源代码
等待一会ing

2、解压

3、看看README写的安装介绍。详细安装方法在INSTALL中
这是linux下gcc开发标准中的文件。

介绍说需要
GTK等一些工具。我们先不管这些，先安装，出问题再解决。

4、bootstrap是初始化configure
缺少一些工具。
这里缺少aclocal
yum install libtool
用上面的命令安装。

5、configure
报错，说c++的编译环境缺少，我们安装gcc-c++
yum install gcc-c++
看到，需要wxGTK等一些工具软件了。
yum install wxGTK
yum install wxGTK-devel
现在缺少libxml
yum install libxml2
继续报错，说明是少动态连接库，安装libxml2-devel
yum install libxml2-devel
继续
yum install libxslt
yum install libxslt-devel

看到，这里少sphinx，但是这个非必须。

6、make ；make install
看到make时出错，少crypto
创建链接，libcrypto.so.x.y.z 是你的 /usr/lib 下已有的某个版本的crypto动态库文件名
ln -s libcrypto.so.x.y.z libcrypto.so
ln -s libcrypto.so.10 libcrypto.so

继续make
7、启动pgadmin
看到缺少libpq.so.5连接库，这是因为lib下找不到。
cd /usr/local
find |grep libpq.so
export POSTGRESQLHOME=/usr/local/pgsql
export LD_LIBRARY_PATH=$POSTGRESQLHOME/lib:/usr/lib64:/usr/local/lib64
./pgadmin3/bin/pgadmin3

8、建立连接
看到我们创建的test库。
(三) pgAdmin使用
1、连接

2、sql编写

3、监控状态