PostgreSQL client utilities

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

Command Line Interface (CLI) utilities

psql (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 foodmart   # TCP/IP

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

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

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:

test=# 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

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:

SELECT status FROM (
  SELECT CONCAT('Connection id:          ', pg_backend_pid()) AS "status", 1
  UNION
  SELECT CONCAT('Current database:       ', current_database(), ' / ', current_schema()), 2
  UNION
  SELECT CONCAT('Current user:           ', current_user), 3
  UNION
  SELECT CONCAT('SSL:                    ', setting), 4 FROM pg_settings WHERE name = 'ssl_ciphers'
  UNION
  SELECT CONCAT('Current pager:          ', 'unknown'), 5
  UNION
  SELECT CONCAT('Using outfile:          ', 'unknown'), 6
  UNION
  SELECT CONCAT('Using delimiter:        ', 'unknown'), 7
  UNION
  SELECT CONCAT('Server:                 ', 'PostgreSQL'), 8
  UNION
  SELECT CONCAT('Server version:         ', setting), 9 FROM pg_settings WHERE name = 'server_version'
  UNION
  SELECT CONCAT('Protocol version:       ', 'unknown'), 10
  UNION
  SELECT CONCAT('Connection:             ', CASE WHEN inet_server_addr() IS NULL THEN 'Localhost via UNIX socket' ELSE CONCAT(inet_server_addr(), ':', inet_server_port()) END), 11
  UNION
  SELECT CONCAT('Server characterset:    ', setting), 12 FROM pg_settings WHERE name = 'server_encoding'
  UNION
  SELECT CONCAT('Db     characterset:    ', pg_encoding_to_char(encoding)), 13 FROM pg_database WHERE datname = current_database()
  UNION
  SELECT CONCAT('Client characterset:    ', setting), 14 FROM pg_settings WHERE name = 'client_encoding'
  UNION
  SELECT CONCAT('Conn.  characterset:    ', 'unknown'), 15
  UNION
  SELECT CONCAT('UNIX socket:            ', s1.setting, '/.s.PGSQL.', s2.setting), 16 FROM pg_settings AS s1 CROSS JOIN pg_settings AS s2 WHERE s1.name = 'unix_socket_directories' AND s2.name = 'port'
  UNION
  SELECT CONCAT('Uptime:                 ', current_timestamp - pg_postmaster_start_time()), 17
ORDER BY 2);

Sources:

• https://www.postgresql.org/docs/current/functions-info.html
• https://www.postgresql.org/docs/current/view-pg-settings.html

MySQL specific SHOW commands

mysql> SHOW DATABASES;

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

mysql> SHOW TABLES; postgres# \t

mysql> SHOW TABLE STATUS …

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

show_global_variables.sql show_processlist.sql show_global_status.sql show_databases.sql show_schemas.sql show_tables.sql test_table.sql

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

Navigation

See also: Navigation.