MariaDB and MySQL shortcuts for PostgreSQL

MySQL SHOW commands

https://dev.mysql.com/doc/refman/8.4/en/show.html

SHOW GLOBAL VARIABLES

-- Improvement SHOW GLOBAL VARIABLES LIKE '%foo%';
-- Improvement SHOW GLOBAL VARIABLES WHERE Variable_name = 'bar';

CREATE OR REPLACE FUNCTION global_variables(variable_name text = '', suppress_statement bool = false)
  RETURNS TABLE(Variable_name text, Value text)
  LANGUAGE SQL AS
$$
  SELECT name, setting FROM pg_settings
$$
;

SELECT * FROM global_variables();
SELECT * FROM global_variables('', true) where variable_name like 'autova%';

CREATE OR REPLACE FUNCTION global_variables2(variable_name2 text = ‘’, suppress_statement bool = false) RETURNS TABLE(Variable_name text, Value text) LANGUAGE plpgsql AS $$ DECLARE sql text := ‘SELECT name, setting FROM pg_settings WHERE TRUE AND setting >= $1’; BEGIN IF 1 IS NOT NULL THEN sql := sql || ’ AND setting <= $1’; END IF;

RETURN QUERY EXECUTE sql USING variable_name2, suppress_statement; END $$ ;

SELECT * FROM global_variables2(); SELECT * FROM global_variables2(’’, true) where variable_name like ‘autova%’;

https://stackoverflow.com/questions/11948131/postgresql-writing-dynamic-sql-in-stored-procedure-that-returns-a-result-set

SHOW PROCESSLIST

Seems to be not neccessary any more??? stats_command_string = true $ pg_ctl reload

SELECT * FROM pg_stat_activity;

https://josuamarcelc.medium.com/show-full-processlist-in-postgresql-d205897bda19

psql

Is the equivalent ot mariadb/mysql CLI.

$ psql –echo-hidden SQL> \df ********* QUERY ********** SELECT n.nspname as “Schema”, p.proname as “Name”, pg_catalog.pg_get_function_result(p.oid) as “Result data type”, pg_catalog.pg_get_function_arguments(p.oid) as “Argument data types”, CASE p.prokind WHEN ‘a’ THEN ‘agg’ WHEN ‘w’ THEN ‘window’ WHEN ‘p’ THEN ‘proc’ ELSE ‘func’ END as “Type” FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> ‘pg_catalog’ AND n.nspname <> ‘information_schema’ ORDER BY 1, 2, 4;

Schemata, Schemas, Databases, Cluster, Namespaces

pSQL> \dn.

pSQL> SELECT nspname FROM pg_catalog.pg_namespace;

pSQL> \dn+

https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql

Functions

https://www.postgresql.org/docs/current/xfunc.html

PostgreSQL provides four kinds of functions:

• query language functions (functions written in SQL) (Section 36.5)
• procedural language functions (functions written in, for example, PL/pgSQL or PL/Tcl) (Section 36.8)
• internal functions (Section 36.9)
• C-language functions (Section 36.10)

pSQL> \df pSQL> \df public.*

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position FROM information_schema.routines LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name WHERE routines.specific_schema=‘my_specified_schema_name’ ORDER BY routines.routine_name, parameters.ordinal_position; List of functions Schema | Name | Result data type | Argument data types | Type ——–+—————————–+—————————————+————————————————————————————————————————————————–+—— public | pg_buffercache_pages | SETOF record | | func public | pg_buffercache_summary | record | OUT buffers_used integer, OUT buffers_unused integer, OUT buffers_dirty integer, OUT buffers_pinned integer, OUT usagecount_avg double precision | func public | pg_buffercache_usage_counts | SETOF record | OUT usage_count integer, OUT buffers integer, OUT dirty integer, OUT pinned integer | func public | show_global_variables | TABLE(variable_name text, value text) | variable_name text | func public | show_global_variables | TABLE(variable_name text, value text) | variable_name text DEFAULT ‘’::text, suppress_statement boolean DEFAULT false | func (5 rows)

postgres=# drop function show_global_variables(variable_name text, suppress_statement bool); DROP FUNCTION

postgres=# drop function show_global_variables(variable_name text); DROP FUNCTION

PostgreSQL data types

Character Types

n is a positive integer. Both of these types can store strings up to n characters (not bytes) in length

https://www.postgresql.org/docs/current/datatype.html

Difference between TEXT and VARCHAR

https://www.postgresql.org/docs/current/datatype-character.html

text is PostgreSQL’s native string data type There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. In most situations text or character varying should be used instead.

User authentification

As explained in Chapter 21, PostgreSQL actually does privilege management in terms of “roles”.

postgres@chef:~$ psql psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1)) Type “help” for help.

oli@chef$ psql psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “oli” does not exist

select * from pg_hba_file_rules ;

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

postgres@chef:~$ ss -tlpn | grep post LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* users:((“postgres”,pid=2168,fd=7)) LISTEN 0 200 [::1]:5432 [::]:* users:((“postgres”,pid=2168,fd=6))

/etc/postgresql/16/main/pg_hba.conf

TYPE DATABASE USER ADDRESS METHOD

Database administrative login by Unix domain socket

local all postgres peer

“local” is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

IPv6 local connections:

host all all ::1/128 scram-sha-256

Allow replication connections from localhost, by a user with the

replication privilege.

local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256

postgres=# select * from pg_hba_file_rules ; rule_number | file_name | line_number | type | database | user_name | address | netmask | auth_method | options | error ————-+————————————-+————-+——-+—————+————+———–+—————————————–+—————+———+——- 1 | /etc/postgresql/16/main/pg_hba.conf | 118 | local | {all} | {postgres} | | | peer | | 2 | /etc/postgresql/16/main/pg_hba.conf | 123 | local | {all} | {all} | | | peer | | 3 | /etc/postgresql/16/main/pg_hba.conf | 125 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | | 4 | /etc/postgresql/16/main/pg_hba.conf | 127 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | | 5 | /etc/postgresql/16/main/pg_hba.conf | 130 | local | {replication} | {all} | | | peer | | 6 | /etc/postgresql/16/main/pg_hba.conf | 131 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | | 7 | /etc/postgresql/16/main/pg_hba.conf | 132 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | | (7 rows)

https://neon.tech/postgresql/postgresql-administration/postgresql-reset-password

TYPE DATABASE USER ADDRESS METHOD

local all oli trust

oli@chef$ psql psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “oli” does not exist

postgres=# CREATE ROLE oli; CREATE ROLE

postgres=# SELECT * FROM pg_roles;

postgres=# \du List of roles Role name | Attributes ———–+———————————————————— oli | Cannot login postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

https://neon.tech/postgresql/postgresql-administration/postgresql-roles

oli@chef$ psql psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: role “oli” is not permitted to log in

postgres=# alter role oli login password ‘’; NOTICE: empty string is not a valid password, clearing password ALTER ROLE

oli@chef$ psql psql: error: connection to server on socket “/var/run/postgresql/.s.PGSQL.5432” failed: FATAL: database “oli” does not exist

postgres=# create database oli; CREATE DATABASE

oli@chef$ psql psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1)) Type “help” for help.