PostgreSQL Client Utilities

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Command Line Interface (CLI) Utilities

Folgende CLI Utilties stehen in PostgresSQL zur Verfügung:

-rwxr-xr-x 1 dba dba   119040 Nov 30 20:39 clusterdb
-rwxr-xr-x 1 dba dba   118752 Nov 30 20:39 createdb
-rwxr-xr-x 1 dba dba   123728 Nov 30 20:39 createuser
-rwxr-xr-x 1 dba dba   114152 Nov 30 20:39 dropdb
-rwxr-xr-x 1 dba dba   114088 Nov 30 20:39 dropuser
-rwxr-xr-x 1 dba dba   906176 Nov 30 20:39 ecpg
-rwxr-xr-x 1 dba dba   218320 Nov 30 20:39 initdb
-rwxr-xr-x 1 dba dba   150328 Nov 30 20:39 pg_amcheck
-rwxr-xr-x 1 dba dba    56616 Nov 30 20:39 pg_archivecleanup
-rwxr-xr-x 1 dba dba   208008 Nov 30 20:39 pg_basebackup
-rwxr-xr-x 1 dba dba   254200 Nov 30 20:39 pgbench
-rwxr-xr-x 1 dba dba    96784 Nov 30 20:39 pg_checksums
-rwxr-xr-x 1 dba dba   197264 Nov 30 20:39 pg_combinebackup
-rwxr-xr-x 1 dba dba    54968 Nov 30 20:39 pg_config
-rwxr-xr-x 1 dba dba    73568 Nov 30 20:39 pg_controldata
-rwxr-xr-x 1 dba dba   157960 Nov 30 20:39 pg_createsubscriber
-rwxr-xr-x 1 dba dba    88816 Nov 30 20:39 pg_ctl
-rwxr-xr-x 1 dba dba   529672 Nov 30 20:39 pg_dump
-rwxr-xr-x 1 dba dba   287880 Nov 30 20:39 pg_dumpall
-rwxr-xr-x 1 dba dba   113624 Nov 30 20:39 pg_isready
-rwxr-xr-x 1 dba dba   126960 Nov 30 20:39 pg_receivewal
-rwxr-xr-x 1 dba dba   126968 Nov 30 20:39 pg_recvlogical
-rwxr-xr-x 1 dba dba    88680 Nov 30 20:39 pg_resetwal
-rwxr-xr-x 1 dba dba   274584 Nov 30 20:39 pg_restore
-rwxr-xr-x 1 dba dba   212728 Nov 30 20:39 pg_rewind
-rwxr-xr-x 1 dba dba    62016 Nov 30 20:39 pg_test_fsync
-rwxr-xr-x 1 dba dba    55472 Nov 30 20:39 pg_test_timing
-rwxr-xr-x 1 dba dba   250456 Nov 30 20:39 pg_upgrade
-rwxr-xr-x 1 dba dba   168008 Nov 30 20:39 pg_verifybackup
-rwxr-xr-x 1 dba dba   138592 Nov 30 20:39 pg_waldump
-rwxr-xr-x 1 dba dba    83656 Nov 30 20:39 pg_walsummary
-rwxr-xr-x 1 dba dba  1062272 Nov 30 20:39 psql
-rwxr-xr-x 1 dba dba   128240 Nov 30 20:39 reindexdb
-rwxr-xr-x 1 dba dba   136960 Nov 30 20:39 vacuumdb

`psql` (entspricht `mariadb`/`mysql`)

In MySQL the most often used CLI is mysql. The equivalent in PostgreSQL is psql.

Connecting to a PostgreSQL Cluster happens as follows:

$ psql -U admin -W secret -h 127.0.0.1 -p 5432 -d foodmart   # TCP/IP

$ psql --username=admin --password=secret --host=127.0.0.1 --port=5432 --dbname=foodmart   # TCP/IP

$ psql --username=admin --password=secret --host=/var/run/postgresql --port=5432 foodmart   # UNIX socket

Folgende Optionen sind in etwa ähnlich:

MySQL	PostgreSQL
–print-defaults
–no-defaults	-X, –no-psqlrc
–defaults-file=#
–defaults-extra-file=#
–defaults-group-suffix=#
-?, -I, –help	-?, –help[=options]
–abort-source-on-error
–auto-rehash
-A, –no-auto-rehash
–auto-vertical-output
-B, –batch
–binary-as-hex
–binary-mode
–character-sets-dir=name
–column-names
-N, –skip-column-names
–column-type-info
-c, –comments
-C, –compress
–connect-expired-password
–connect-timeout=#
-D, –database=name	-d, –dbname=DBNAME
-#, –debug[=#]
–debug-check
-T, –debug-info
–default-auth=name
–default-character-set=name
–delimiter=name
–enable-cleartext-plugin
-e, –execute=name	-c, –command=COMMAND
-f, –force
-h, –host=name	-h, –host=HOSTNAME
-H, –html	-H, –html
-i, –ignore-spaces
–init-command=name
–line-numbers
-L, –skip-line-numbers
–local-infile
–max-allowed-packet=#
–max-join-size=#
-G, –named-commands
–net-buffer-length=#
-b, –no-beep
-o, –one-database
–pager[=name]
-p, –password[=name]	-W, –password
–plugin-dir=name
-P, –port=#	-p, –port=PORT
–print-query-on-error	-b, –echo-errors
–progress-reports
–prompt=name
–protocol=name
-q, –quick
–quick-max-column-width=#
-r, –raw
–reconnect
-U, –safe-updates
-U, –i-am-a-dummy
–sandbox
–secure-auth
–select-limit=#
–server-arg=name
–show-query-costs
–show-warnings
–sigint-ignore
-s, –silent	-q, –quiet
-S, –socket=name	–host=SOCKETDIR
–ssl
–ssl-ca=name
–ssl-capath=name
–ssl-cert=name
–ssl-cipher=name
–ssl-key=name
–ssl-crl=name
–ssl-crlpath=name
–tls-version=name
–ssl-fp=name
–ssl-fplist=name
–ssl-verify-server-cert
-t, –table	–csv
–tee=name
-n, –unbuffered
-u, –user=name	-U, –username=USERNAME
-v, –verbose
-V, –version	-V, –version
-E, –vertical
-w, –wait
-X, –xml
< FILENAME	-f, –file=FILENAME
–execute=‘show databases’	-l, –list
–init-command=‘SET GLOBAL …’	-v, –set=, –variable=NAME=VALUE
>/tmp/test	-o, –output=FILENAME
	-1 (“one”), –single-transaction
	-a, –echo-all
	-e, –echo-queries
	-E, –echo-hidden
	-L, –log-file=FILENAME
	-n, –no-readline
	-s, –single-step
	-S, –single-line
	-A, –no-align
	-F, –field-separator=STRING
	-P, –pset=VAR[=ARG]
	-R, –record-separator=STRING
	-t, –tuples-only
	-T, –table-attr=TEXT
	-x, –expanded
	-z, –field-separator-zero
	-0, –record-separator-zero

The MySQL CLI knows about the following commmands:

mysql> \h

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command, if enabled
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...) for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout or file

For server side help, type 'help contents'

The equivalents in psql are:

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

MySQL	PostgreSQL
? (?)	?
clear (\c)	\r
connect (\r)	\c[onnect]
delimiter (\d)	n.a.
edit (\e)	\e
ego (\G)	\gx
exit (\q)	\q
go (\g)	\g
help (\h)	\h
nopager (\n)	\pset pager off
notee (\t)	\o
pager (\P)	\setenv PAGER ‘cut -b10’
print (\p)	n.a.
prompt (\R)	\set PROMPT1 ‘…’
quit (\q)	\q
rehash (#)	n.a.
source (.)	\i FILE
status (\s)	See status command
system (!)	! [COMMAND]
tee (\T)	\o [FILE]
use (\u)	\c[onnect]
charset (\C)	\encoding
warnings (\W)	n.a.
nowarning (\w)	n.a.
resetconnection (\x)	n.a.

CLI commands

Some usefull examples in `psql`:

postgres# \timing

Source: psql

MySQL CLI command `status`

mysql> status
--------------
mysql  Ver 8.4.7 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          13
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.4.7 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /run/mariadb/mariadb-3350.sock
Binary data as:         Hexadecimal
Uptime:                 9 hours 50 min 54 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 119  Flush tables: 3  Open tables: 38  Queries per second avg: 0.000
--------------

The approximate equivalent in PostgreSQL is (script see further down):

postgres# \cd /home/oli/fromdual_devel/myenv/sql/postgresql
postgres# \set ECHO queries
postgres# \pset pager 0
postgres# \i status.sql
                     status
---------------------------------------------------
 Connection id:          2777972
 Current database:       postgres / public
 Current user:           mysql
 SSL:                    none
 Current pager:          unknown
 Using outfile:          unknown
 Using delimiter:        unknown
 Server:                 PostgreSQL
 Server version:         19devel
 Protocol version:       unknown
 Connection:             Localhost via UNIX socket
 Server characterset:    SQL_ASCII
 Db     characterset:    SQL_ASCII
 Client characterset:    UTF8
 Conn.  characterset:    unknown
 UNIX socket:            /tmp/.s.PGSQL.5433
 Uptime:                 05:14:47.270966

Sources:

MySQL specific `SHOW` commands

mysql> SHOW DATABASES;
$ psql -l
postgres# \l

See also differences between MySQL and PostgreSQL related to: schema and database.

mysql> SHOW TABLES;
postgres# \dt

and

mysql> SHOW TABLE STATUS
postgres=# SELECT * FROM information_schema.tables;

Scripts

Some script which help to survive (they can be found in the MyEnv):

show_create_table.sh
show_databases.sql
show_global_status.sql
show_global_variables.sql
show_processlist.sql
show_schemas.sql
show_tables.sql
status.sql
test_table.sql

How to use the SQL scripts:

postgres# \cd /home/oli/fromdual_devel/myenv/sql/postgresql
postgres# \set ECHO queries
postgres# \pset pager 0
postgres# \i show_global_variables.sql

How to use the bash script:

$ show_create_table.sh DATABASE SCHEMA TABLE

TODO: world.sql.gz and Foodmart!

other CLI tools (mariadb-dump, …)

Graphical User Interfaces (GUI)

Fat client GUI

Similar to MySQL Workbench:

pgAdmin (Web and Fat client), Source, Installation, Access or /usr/pgadmin4/bin/pgadmin4

Web-GUI

Admin tools

Similar to phpMyAdmin:

[phpPgAdmin], Source 1, Source 2, Installation: apt install phppgadmin, Access - Not clear to me which is the right version???
Adminer, Source, Installation, Access

ER diagrammer

DBeaver ER Diagrams Similar to MySQL workbench