View source for Linux - Databases

== MySQL ==

After install: mysqladmin -u root password 'newpassword'
* /etc/my.cnf

== Postgres ==

See also [https://fedoraproject.org/wiki/PostgreSQL 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 [https://www.postgresql.org/docs/12/app-reindexdb.html reindexdb] or [https://www.postgresql.org/docs/12/sql-reindex.html REINDEX].

 reindex -h localhost -U postgres
 vacuumdb h localhost -U postgres --all --analyze-in-stages

remove old database:
 rm -rf /var/lib/pgsql/data-old

=== psql ===

 psql -h localhost -U someuser dbname

See also [https://www.postgresql.org/docs/current/static/app-psql.html here] for meta-commands:

{| class="wikitable"

!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 [https://www.pgadmin.org/download/windows.php PGAdmin] Console.