Linux - Databases

MySQL

After install: mysqladmin -u root password 'newpassword'

• /etc/my.cnf

Postgres

See also here.

Initialisation

Install Packages:

• postgresql-server postgresql-libs postgresql postgresql-contribsystem php-pgsql postgresql-devel php-pecl-apcu

Also for python:

• python-devel redhat-rpm-config python3-postgresql

postgresql-setup --initdb
firewall-cmd --permanent --add-port=5432/tcp

Suggested changes to postgresql.conf:

listen_addresses = '*'
password_encryption = scram-sha-256
shared_buffers = 256MB
work_mem = 64MB
effective_cache_size = 4GB # Not allocation: guide to OS cache size

Temporary setting in pg_hba.conf to allow local postgres connection:

local    all            all       trust

su - postgres
psql

then change postgres user's password. These entries set password-based authentication:

host    all             all              127.0.0.1/32           scram-sha-256
host    all             all              ::1/128                scram-sha-256
host    all             all              192.168.1.0/24         scram-sha-256
host    everywheredb    all              0.0.0.0/0              scram-sha-256
host    everywheredb    all              ::0/0                  scram-sha-256

Upgrades

After major upgrades (requires package postgresql-upgrade):

postgresql-setup --upgrade

Also, reindex.with reindexdb or REINDEX.

psql

psql -h localhost -U someuser dbname

See also here for meta-commands:

Meta Command	Description
\list [pattern]	List databases
\connect somedatabase	Connect to somedatabase
\d[+] [pattern]	Show all tables, views, sequences, etc
\d+ [tablename]	Describe the table
\l+ [pattern]	Show all databases
\dn	Show all schemas
\dt	Show details of tables
\du[+] [pattern]	Show all users (roles)
\i /somepath/somefile.sql	Execute commands from file
\password	Change password

Dump data from a query to CSV:

\copy (select * from sometable) to '/tmp/somefile.csv' with csv delimiter ',';

• "public" schema always exists.
• show search_path;
• Template1 copied when a new database is created

Useful SQL

Create user (aka "role"):

CREATE USER jsmith WITH nocreatedb nocreaterole nosuperuser PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE "dbname" TO jsmith;

Change password:

ALTER USER jsmith WITH PASSWORD 'secret';

Add privileges:

GRANT USAGE ON SCHEMA sales to jsmith;
GRANT CREATE ON SCHEMA sales to jsmith;

Show shadow table:

SELECT * FROM PG_SHADOW;

Drop all tables in a schema:

SELECT 'DROP TABLE IF EXISTS "' || tablename || '" CASCADE;' 
FROM pg_tables
WHERE schemaname = 'public';

Change owner of table:

ALTER TABLE mathoid OWNER TO WIKI;

Backup and Restore

pg_dump -h localhost -p 5432 -d DATABASE -U postgres -f /tmp/db.dmp
psql -h localhost -p 5432 -d DATABASE -U postgres < /tmp/db.dmp

Admin Console

Use the PGAdmin Console.