Bases de dades / Databases

Índex / Index

General
SQL
- Ordres / Commands
- MySQL / MariaDB
- Oracle
- PostgreSQL
- SQLite
NoSQL
- MongoDB
- Redis
Time series
- InfluxDB
Replicació / Replication
Cerca / Search
Disseny / Design
AWS

General

			W3-mSQL	PHP3 (es) (ch)	JDBC
	Info	GUI		LBD/EPFL Manual Tutorial
miniSQL	mSQL Keeper	xsqlmenu	W3-mSQL guide
MySQL	Mail archive Introduction (Linux Journal 67) Getting started	MySQL GUI Tools mySQLGUI XmySQL kmysqladmin		Anthony Awtrey Consulting
MySQL		GtkSQL Gnome-db Kexi (kde) sqlgui (kde)		Anthony Awtrey Consulting
PostgreSQL		GtkSQL Gnome-db Kexi (kde) sqlgui (kde)		Linux Journal (67) PHP3 manual	Linux Journal (67)
PostgreSQL		PgAccess		Linux Journal (67) PHP3 manual	Linux Journal (67)

SQL

Comparison of relational database management systems
Firebird
MariaDB (fork from MySQL) (SkySQL) (wp)
MySQL
- Utilitats
PostgreSQL
SQLite

commands:

			MySQL	sqlite	PostgreSQL	InfluxDB (CLI)
admin user			root		postgres
default database					postgres
CLI (from shell)	user	create a user			`su - postgres` sudo su - postgres `createuser --createdb my_user`
	user	delete a user			`dropuser`
	database	create a database			`createdb` `--username my_user my_db`
	database	delete a database			`dropdb`
	statistics				`pg_stat_activity`
					`pg_cancel_backend`
	backup database	backup one database		`sqlite3 sqlite.db .dump >toto.sql`	`pg_dump` `[-h <host>] [-p <port>] [-t <table>] my_db` `[\| gzip] >my_db.sql[.gz]`
		restore one database			(create users) `createdb [-T template0] my_db` `psql [--set ON_ERROR_STOP=on] [--single-transaction] my_db <my_db.sql` or: `cat my_db.sql.gz \| gunzip \| psql my_db`
		backup one database (custom dump format)			`pg_dump -Fc my_db >my_db.sql`
		restore one database (custom dump format)			`pg_restore-d my_db my_db.sql`
	backup cluster	backup all databases in cluster			`pg_dumpall >my_cluster.sql`
		backup cluster structure only			`pg_dumpall--globals-only` `>my_cluster.sql`
		restore cluster			`psql -f` `my_cluster.sql postgrescat my_cluster.sql.gz \| gunzip \| psql postgres`
	connection	connection to server	`mysql -u my_user -p`	`sqlite3 toto.db`	`psql --username my_user [-h <host>] [-p <port>] my_db` `su postgres -c "/usr/pgsql-9.5/bin/psql"` sudo su postgres psql	`influx -precision rfc3339`
commands (once connection has been established)	general	help		`.help`	`\?`
	general	info			`\conninfo`
	database	show databases	`show databases;`		`\l`
		use database	`use toto_db`		`\c toto_db`
		show tables	`show tables;`	`.tables` `.schema`	`\dt`
		schema of a table	`desc` `table_name;`	`.schema table_name`	`\d table_name`
		extensions in connected database			`\dx`
		users			`\du`	`SHOW USERS`
		processes	`show processlist;`
		max connections	`SHOW VARIABLES LIKE 'max_connections';`
			SQL			InfluxQL
	database	create database	`CREATE DATABASE toto_db;`			`CREATE DATABASE mydb`
		list of databases				`SHOW DATABASES`
		use a database				`USE mydb`
		insert a value				`INSERT <measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]`
		retrieve values				`SELECT ... FROM ... [WHERE ...] [LIMIT ...]`

SQL Language (wp)
- MySQL 5.7 Reference Manual
  - 11. Data types
    - 11.3 Date and time types
      - `tempsCreacio` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
- SQL Tutorial (Zentut)
- Creation of a database
  - CREATE DATABASE toto_db;
- Deletion of a database
  - DROP DATABASE toto_db;
- Creation of a user
  - CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
  - At the same time of database creation:
    - CREATE DATABASE IF NOT EXISTS mydatabase_db; GRANT ALL ON mydatabase_db.* TO 'mydatabase_user'@'%' IDENTIFIED BY '...'; GRANT ALL ON mydatabase_db.* TO 'mydatabase_user'@'localhost' IDENTIFIED BY '...'; FLUSH PRIVILEGES;
- Esborra una fila en una taula.
  - DELETE FROM toto_table WHERE camp = 'S';
  - segons la data / according to date:
    - How to delete records from table where timestamp field < now()-44 minute?
  - esborrat en cadena en taules relacionades / chain deletion in related tables
    - SQL DELETE
      - FOREIGN KEY Constraint
    - 1.8.5.4 Foreign key differences
      - InnoDB
        
        ON DELETE
      - MyISAM
        
        Multi-table Deletes
    - Multiple table DELETE example (MyISAM):
      - DELETE toto1, toto2, toto3 FROM toto1 INNER JOIN toto2 INNER JOIN toto3 WHERE toto1.tempsCreacio < CURRENT_TIMESTAMP - interval '12' hour AND toto2.id=toto1.id AND toto3.id=toto1.id;
- Afegeix una columna (camp) a una taula (ALTER, NOT NULL):
  - ALTER TABLE toto_table ADD COLUMN toto_field varchar(10) NOT NULL
- Esborra totes les files d'una taula:
  - TRUNCATE TABLE toto_table;
- Esborra una taula de la base de dades:
  - DROP TABLE toto_table;
- Afegeix un element a una taula
  - INSERT INTO nom_taula(columna1, columna2) VALUES ('first value_1', 'second_value_1'), ('first_value_2','second_value_2');
- Mostra el contingut d'una taula
  - SELECT * FROM toto_table;
- Subqueries
  - Correlated subquery (wp)
    - Optimizing correlated subqueries
  - CREATE VIEW toto1_view AS
    SELECT id AS toto1ID FROM toto1 WHERE tempsCreacio < CURRENT_TIMESTAMP - interval '20' minute; SELECT * FROM toto2 NATURAL JOIN toto1_view WHERE toto2.id = toto1ID;
  - DELETE toto2 FROM toto2 INNER JOIN toto1_view WHERE toto2.id = toto1ID;
  - DROP VIEW toto1_view;

NoSQL (document-oriented)

NOSQL Databases
NoSQL (wp)
Implementacions / Implementations
- BigTable (Google) (wp)
- Cassandra (Facebook) (wp)
- CouchDB
- MongoDB (wp)
- Redis

Replicació / Replication

Providers
- AWS database
Multi-master replication (wp)
- Galera replication (Codership)
  - MariaDB Galera Cluster
    - MariaDB Galera Cluster 5.5.29 Stable (GA) released
Replica sets
- MongoDB
Master-slave (wp)

Cerca / Search

Lucene (Apache)
- Mediawiki: Extension:Lucene-search
Sphinx
- Mediawiki: Extension:SphinxSearch

Disseny / Design

AWS

AWS database
- AWS RDS
- AWS Aurora

MongoDB

MySQL / MariaDB

MySQL (es)
- Install and set the root password
- MySQL Workbench
  - urpmi mysql-workbench [python-sqlite2]
- MySQL Tools
  - MySQL Migration Toolkit
  - MySQL Administrator
    - urpmi mysql-administrator
    - Server hostname: localhost (127.0.0.1 does not work)
  - MySQL Query Browser
    - urpmi mysql-query-browser
Utilitats / Utilities
- Contrasenya root / Root password
- Accés remot / Remote access
- Time zones
- Config
  - /etc/mysql/my.cnf
    - max_connections = 151
Problemes / Problems
- InnoDB: Fatal error: cannot allocate memory for the buffer pool
- Too many connections
- Django: "Too many connections"

Oracle

PostgreSQL

Instal·lació / Installation

Mageia
- urpmi postgresql9.4-server
- systemctl enable postgresql.service
- systemctl start postgresql.service
CentOS
- Installation from PostgreSQL.org repos
- YUM installation
  - yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm
  - yum install postgresql94-serverpostgresql94-contrib
  - /usr/pgsql-9.4/bin/postgresql94-setup initdb
  - systemctl enable postgresql-9.4.service
  - systemctl start postgresql-9.4.service
  - systemctl status postgresql-9.4.service
- version from CentOS original repository (9.2):
  - yum install postgresql-server
  - postgresql-setup initdb
  - systemctl enable postgresql.service
  - systemctl start postgresql.service

	repo	version	bin dir	config dir	database dir	service
CentOS	original repo	9.2	/bin/	/var/lib/pgsql/data/	/var/lib/pgsql/data/base/	systemctl start postgresql.service
CentOS	postgresql.org	9.4	/usr/pgsql-9.4/bin/	/var/lib/pgsql/9.4/data/	/var/lib/pgsql/9.4/data/base/	systemctl start postgresql-9.4.service
Mageia	distribution	9.4	/usr/bin/	/var/lib/pgsql/data/	/var/lib/pgsql/data/base/	systemctl start postgresql.service
Ubuntu	distribution	9.3		/etc/postgresql/9.3/main/	/var/lib/postgresql/9.3/main/base/	sudo service postgresql start

Get running version:
- # pgsql_version=$(su postgres -c "psql -c 'SELECT version()'" | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
- This version will not work inside a root crontab: "sudo: sorry, you must have a tty to run sudo"
  - $ psql_version=$(sudo -u postgres psql postgres -c 'SELECT version()' | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')

Configuració / Setup

authentication
- What is the Default Password for PostgreSQL?
- Authentication Methods
- /var/lib/pgsql/[<version>]/data/pg_hba.conf
  - password check (sent in md5):
    - host all all 127.0.0.1/32 md5
  - no password check:
    - host all all 127.0.0.1/32 trust
- Fabric
  - from fabric.contrib.files import sed def postgresql_install(): ... # spaces are important sed('/var/lib/pgsql/data/pg_hba.conf', 'host all all 127.0.0.1/32 ident', 'host all all 127.0.0.1/32 md5', use_sudo=True )
- Django (GIS)
  - DATABASES = { 'default': { 'ENGINE': 'django.contrib.gis.db.backends.postgis', 'NAME': '...', 'USER': '...', 'PASSWORD': open(os.path.join(BASE_DIR, 'db_p.txt'),'r').read().strip(), 'HOST': '127.0.0.1', 'PORT': '', # Set to empty string for default. (e.g. 5432 for PostgreSQL) } }
accessible from other computers (e.g.: 172.31.0.0/16)
- /var/lib/pgsql/data/postgresql.conf
  - listen_addresses = '*'
- /var/lib/pgsql/data/pg_hba.conf
  - host all all 172.31.0.0/16 md5
- systemctl restart postgresql.service
- open firewall: TCP 5432
logs and statistics
- sudo journalctl -u postgresql.service
- auto_explain
  - /var/lib/pgsql/<version>/data/postgresql.conf
    - shared_preload_libraries = 'auto_explain' auto_explain.log_analyze = true auto_explain.log_min_duration = 20ms
  - tail -n 200 -f /var/lib/pgsql/<version>/data/log/postgresql-xxx.log
  - tail -n 200 -f /var/lib/pgsql/<version>/data/pg_log/postgresql-xxx.log
- pg_stat_statements
  - setup
    - /var/lib/pgsql/<version>/data/postgresql.conf
      - shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all
    - su postgres
      - psql
        
        CREATE EXTENSION pg_stat_statements;
    - ...
  - reset stats
    - su postgres
      - psql
        
        SELECT pg_stat_statements_reset();
  - test with pgbench, to have some values on stats
    - su postgres
    - /usr/pgsql-10/bin/pgbench -i mydb (try also with postgres database)
    - /usr/pgsql-10/bin/pgbench -c10 -t300 mydb
  - view stats
    - su postgres
      - psql
        
        \x
        
        SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    - sudo -u postgres sh -c "psql postgres -c '\x' -c 'SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC;'" >/tmp/mystats.txt
    - dump to a csv file
      - Postgres tips
      - \copy (select * from pg_stat_statements ORDER BY total_time / calls DESC) TO pg_stat_statements.csv CSV HEADER DELIMITER ','
    - dump to a sql file:
      - Export only views in Postgres
        
        Postgres dump of only parts of tables for a dev snapshot
      - ...
    - ...
  - Problemes / Problems
    - postgres=# SELECT pg_stat_statements_reset();
      ERROR: function pg_stat_statements_reset() does not exist
      - pg_stat_statements enabled, but the table does not exist
      - Solució / Solution
        
        check whether the extension is installed:
        
        psql
        \dx
        
        psql
        SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and installed_version is not null;
        
        if not, install it:
        
        psql
        CREATE EXTENSION pg_stat_statements;
create a superuser named 'my_su'
- sudo su - postgres
- createuser --superuser my_su
- exit
create a user named 'geo_user', which can create databases
- bash
  - Mageia
    - option 1: password specified interactively
      - createuser --username postgres --createdb geo_user
    - option 2: specified password
      - psql --username postgres--command="CREATE USER geo_user WITH PASSWORD 'xxxx' CREATEDB;"
  - CentOS
    - as, by default, access is "ident", running commands as master_user (postgres) will have superuser access
    - sudo su - postgres
    - option 1: password specified interactively
      - createuser --createdb geo_user
    - option 2: specified password
      - psql --command="CREATE USER geo_user WITH PASSWORD 'xxxx' CREATEDB;"
      - to avoid first step "sudo su - postgres":
        
        sudo su - postgres -s /bin/bash -c "psql --command \"CREATE USER geo_user WITH PASSWORD '$(cat /tmp/db_p.txt)' CREATEDB;\""
    - exit
- Fabric
  - env.db_user = 'geo_user' env.postgresql_master_user = 'postgres' put('db_p.txt','/tmp') sudo('psql --command "CREATE USER %(db_user)s WITH PASSWORD \'$(cat /tmp/db_p.txt)\' CREATEDB;"' % env, user='%(postgresql_master_user)s' % env)
create a database as user geo_user
- optionally, to avoid having to specify a password, create the file:
  - echo '127.0.0.1:5432:postgres:geo_user:xxxx' > ~/.pgpass
  - chmod 600 ~/.pgpass
- createdb -h 127.0.0.1 --username=geo_user geodjango

	cli tools	psql
create a superuser	`createuser --superuser my_su`
create a user that can create databases	`createuser` `[-hdb_host]--username master_user --createdb my_user`	`psql [-h db_host]` `--username master_user--command="CREATE USER my_user WITH PASSWORD 'xxxx' CREATEDB;"`
create a database	`createdb [-h db_host] --username=my_user my_database`	`psql [-h db_host]` `--username my_user--command="CREATE DATABASE my_user;"`

NOTES:

usually, master_user is postgres
in previous examples, my_user was geo_user
if not specified, db_host is 127.0.0.1

check the connectivity to the database
- optionally, to avoid having to specify a password, modify the file:
  - echo '127.0.0.1:5432:geodjango:geo_user:xxxx' >> ~/.pgpass
- psql -h 127.0.0.1 --username=geo_user geodjango
remove a user
- sudo su - postgres
- dropuser geo_user
- exit
completely remove database server data:
- sudo rm -rf /var/lib/pgsql/9.4/data
- old versions (without subdir for version)
  - sudo rm -rf /var/lib/pgsql/data
- then you can run "postgresql-setup initdb" again: it will create and populate /var/lib/pgsql/data
9.26. System Administration Functions
- list of active connections:
  - SELECT * FROM pg_stat_activity;

Ús de disc / Disk usage
- Disk Usage
  - SQL snippets
  - to execute the snippets you must first connect to the server and choose a database:
    - su postgres -c "/usr/pgsql-9.5/bin/psql"
    - \l
    - \c my_database

PostGIS (.org)

GeoDjango
Installation
- Mageia
  - Mageia 8
  - Mageia 7
    - urpmi postgis
  - On Mageia 6, server is version 9.6, but package postgis (2.2) has been compiled with version 9.5
    - ERROR: incompatible library "/usr/lib64/postgresql/postgis-2.2.so": version mismatch DETAIL: Server is version 9.6, library is version 9.5.
    - Installer Ekylibre v2 sous Mageia 6
    - Solució / Solution
      - Install postgis 2.3 from source:
        
        Installer PostGIS 2.3 requis pour le serveur Postgresql 9.6 ou supérieur
        
        Dependències / Dependencies
        
        urpmi postgresql9.6-devel lib64gdal-devel
        
        Passos / Steps
        
        wget https://download.osgeo.org/postgis/source/postgis-2.5.2.tar.gz
        
        tar xvzf postgis-2.5.2.tar.gz
        
        cd postgis-2.5.2
        
        ./configure ...
        
        make
        
        sudo make install
  - Mageia <6
    - urpmi postgis
- Alma 8
- CentOS
  - yum install postgis
- Only client
  - when server is at e.g. AWS RDS, and we need to run manage.py migrate
  - CentOS
    - yum install geos gdal
Setup
- Installing PostGIS (GeoDjango)
- add postgis extension to the database (used user must have root privileges)
  - psql --username postgres my_database--command "CREATE EXTENSION postgis;"
- Problemes / Problems
  - ERROR: could not load library "/usr/lib64/postgresql/postgis-2.5.so": /usr/lib64/postgresql/postgis-2.5.so: undefined symbol: SearchSysCache3
    - migration from an older version
  - ERROR: could not open extension control file "/usr/share/postgresql/extension/postgis.control": No such file or directory
Get list of extensions
- \c my_database; \dx
Get version
- \c my_database; SELECT postgis_full_version();
- Problems
  - when using PostgreSQL 10, PGSQL still points to 96:POSTGIS="2.5.5" [EXTENSION] PGSQL="96" (procs need upgrade for use with "100") GEOS="3.9.1-CAPI-1.14.2" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER
    - Use of deprecated function
    - Solution
      - ALTER EXTENSION postgis UPDATE to "2.5.5next"; ALTER EXTENSION postgis UPDATE to "2.5.5"; SELECT postgis_full_version();
        
        POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.1-CAPI-1.14.2" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.1" LIBJSON="0.11" LIBPROTOBUF="1.0.2" RASTER

Upgrade

Upgrading PostGIS on Centos 7
3.4 Upgrading spatial databases (old)
- ALTER EXTENSION postgis UPDATE TO "2.0.2";

	uninstall old versions of postgis	install new versions of postgis	restart server	update extension in SQL for every database using GIS
PostgreSQL 9.6 / GIS 2.2 -> PostgreSQL 9.6 / GIS 2.3	`sudo rpm -e postgis22_96`	`sudo yum -y install postgis23_96`	`sudo systemctl restart postgresql-9.6.service`	`sudo -i su postgres # update psql -c 'alter extension postgis update' -d mydatabase_with_gis # check new version psql -c 'select postgis_full_version()' -d mydatabase_with_gis`
PostgreSQL 10 / GIS 2.5 -> PostgreSQL 10 / GIS 3.0	`sudo rpm -e postgis25_10`	`sudo dnf -y install postgis30_10`	`sudo systemctl restart postgresql-10.service`	`sudo -i su postgres # updatepsql -c 'ALTER EXTENSION postgis UPDATE;' -d mydatabase_with_gispsql -c 'SELECT postgis_extensions_upgrade();' -d mydatabase_with_gis # check new version psql -c 'SELECT postgis_full_version();' -d mydatabase_with_gis`
PostgreSQL 11 / GIS 3.0 -> PostgreSQL 11 / GIS 3.1	`sudo rpm -e postgis30_11`	`sudo dnf -y install postgis31_11`	`sudo systemctl restart postgresql-11.service`	`sudo -i su postgres # updatepsql -c 'ALTER EXTENSION postgis UPDATE;' -d mydatabase_with_gispsql -c 'SELECT postgis_extensions_upgrade();' -d mydatabase_with_gis # check new version psql -c 'SELECT postgis_full_version();' -d mydatabase_with_gis`

Steps (e.g. 9.6 with GIS 2.2 -> 9.6 with GIS 2.3)
1. stop server
  - sudo systemctl stop postgresql-9.6.service
2. uninstall old versions of postgis
  - sudo rpm -e postgis22_96
3. install new versions of postgis
  - sudo yum -y install postgis23_96
4. restart server
  - sudo systemctl start postgresql-9.6.service
5. update extension in SQL for every database using gis:
  - sudo -i su postgres # update psql -c 'alter extension postgis update' -d mydatabase_with_gis # check new version psql -c 'select postgis_full_version()' -d mydatabase_with_gis
Problems
- $ psql -c 'select postgis_full_version()' -d my_database could not change directory to "/root" ERROR: could not access file "$libdir/rtpostgis-2.5": No such file or directory CONTEXT: SQL statement "SELECT public.postgis_gdal_version()"
  - Solució / Solution:
    - ALTER EXTENSION postgis UPDATE; SELECT postgis_extensions_upgrade();SELECT postgis_full_version();
  - optionally:
    - check psql version:
      - psql
        psql (9.2.24, server 10.17)
        WARNING: psql version 9.2, server version 10.0.
        Some psql features might not work.
    - check installed old versions
      - dnf list --installed "postgre*"
    - uninstall old versions
    - reinstall latest version (to force symbolic ink to psql binary)
      - dnf reinstall ...
    - make sure that you are using the correct version of psql
      - /usr/pgsql-10/bin/psql -c 'select postgis_full_version()' -d my_database

Versions:

		postGIS
postgreSQL	9.5	2.2
	9.6	2.2	2.3	2.4	2.5	2.6	3.0
	10		2.3	2.4	2.5		3.0
	11			2.4	2.5		3.0	3.1
	12				2.5		3.0	3.1
	13						3.0	3.1

Backup

Ordres SQL / SQL commands

SQL Dump

Install
- CentOS
  - sudo yum install postgresql96-contrib
  - sudo yum install holland-pgdump

Usage

create	recover
pg_dumpall >mydump.sql	psql -f mydump.sql
pg_dump	pg_restore

pg_dumpall (to create dump file
- $ sudo su - postgres -c "/usr/pgsql-${pgsql_version}/bin/pg_dumpall | gzip > /tmp/db_dump_$(date '+%Y%m%d_%H%M').sql.gz"
- # su postgres -c "/usr/pgsql-${pgsql_version}/bin/pg_dumpall | gzip > /tmp/db_dump_$(date '+%Y%m%d_%H%M').sql.gz"
- version-aware:
  - # get version of running postgresql #pgsql_version=$(sudo -u postgres psql postgres -c 'SELECT version()' | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')pgsql_version=$(su postgres -c "psql -c 'SELECT version()'" | awk '$1 ~ /PostgreSQL/ {print $2}' | egrep -o '[0-9]{1,}\.[0-9]{1,}')
    pg_dumpall_bin="/usr/pgsql-${pgsql_version}/bin/pg_dumpall" # create dump file su postgres -c "${pg_dumpall_bin} | gzip > $dump_path"
psql (to recover from dump file)
- /usr/pgsql-${pgsql_version}/bin/psql -fmy_cluster.sql postgres
- # su postgres -c "cat /abs/path/to/my_cluster.sql.gz | gunzip |/usr/pgsql-${pgsql_version}/bin/psql postgres"
- Monitor progress
  - how to track progress of a large postrgresql dump
    - use pv (pipe viewer)
pg_dump
- /usr/pgsql-${pgsql_version}/bin/pg_dump
  - -s (only schema)
pg_restore
Performance
- PostgreSQL: improving pg_dump, pg_restore performance

Upgrade

Upgrade PostGIS

Upgrading a PostgreSQL Cluster (9.4). Use one of the following methods:

pg_upgrade (pg_migrator)

PostgreSQL upgrade on CentOS (9.5 -> 9.6)

	1. install packages (sudo)	2. init database (will create subdirs in /var/lib/pgsql/xx/data/) (sudo)	3. check upgrade (sudo)	4. stop old service (sudo)	5. run upgrade (sudo)	6. copy config files (sudo)	7. start new service (sudo)	8. analyse cluster (sudo)	9. disable old, enable new services (sudo)	10. update gis (procs need upgrade for use with PostgreSQL xxx")
9.5 -> 9.6	`yum install postgresql96-server postgresql96-contrib postgis22_96`	`/usr/pgsql-9.6/bin/postgresql96-setup initdb`	`cd /tmp` `su postgres` `/usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check`	`systemctl stop postgresql-9.5.service`	`cd /tmp` `su postgres` `/usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/ --new-datadir=/var/lib/pgsql/9.6/data/`	`cp /var/lib/pgsql/9.5/data/postgresql.conf/var/lib/pgsql/9.6/data/postgresql.conf` `cp /var/lib/pgsql/9.5/data/pg_hba.conf/var/lib/pgsql/9.6/data/pg_hba.conf`	`systemctl start postgresql-9.6.service`	`cd /tmp` `su postgres` `./analyze_new_cluster.sh`	`systemctl disable postgresql-9.5.service` `systemctl enable postgresql-9.6.service`
9.6 -> 10	`yum install postgresql10-server postgresql10-contrib postgis25_10`	`/usr/pgsql-10/bin/postgresql-10-setup initdb`	`cd /tmp` `su postgres` `/usr/pgsql-10/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin/ --new-bindir=/usr/pgsql-10/bin/ --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/10/data/ --check` ... Clusters are compatible exit	`systemctl stop postgresql-9.6.service`	`cd /tmp` `su postgres` `/usr/pgsql-10/bin/pg_upgrade --old-bindir=/usr/pgsql-9.6/bin/ --new-bindir=/usr/pgsql-10/bin/ --old-datadir=/var/lib/pgsql/9.6/data/ --new-datadir=/var/lib/pgsql/10/data/` ... Upgrade Complete exit	`cp /var/lib/pgsql/9.6/data/postgresql.conf/var/lib/pgsql/10/data/postgresql.conf` `cp /var/lib/pgsql/9.6/data/pg_hba.conf/var/lib/pgsql/10/data/pg_hba.conf`	`systemctl start postgresql-10.service`	`cd /tmp` `su postgres` `./analyze_new_cluster.sh` exit	`systemctl disable postgresql-9.6.service` `systemctl enable postgresql-10.service`
10 -> 11	`dnf install postgresql11-server postgresql11-contrib postgis30_11`	`/usr/pgsql-11/bin/postgresql-11-setup initdb`	`cd /tmp` `su postgres` `/usr/pgsql-11/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/10/data/ --new-datadir=/var/lib/pgsql/11/data/ --check` ... Clusters are compatible exit	`systemctl stop postgresql-10.service`	`cd /tmp` `su postgres` `/usr/pgsql-11/bin/pg_upgrade --old-bindir=/usr/pgsql-10/bin/ --new-bindir=/usr/pgsql-11/bin/ --old-datadir=/var/lib/pgsql/10/data/ --new-datadir=/var/lib/pgsql/11/data/` ... Upgrade Complete exit	`cp /var/lib/pgsql/10/data/postgresql.conf/var/lib/pgsql/11/data/postgresql.conf` `cp /var/lib/pgsql/10/data/pg_hba.conf/var/lib/pgsql/11/data/pg_hba.conf`	`systemctl start postgresql-11.service` `systemctl status postgresql-11.service`	`cd /tmp` `su postgres` `./analyze_new_cluster.sh` exit	`systemctl disable postgresql-10.service` `systemctl enable postgresql-11.service`	`su postgres` `psql -c 'ALTER EXTENSION postgis UPDATEto "3.0.3next";' -d mydatabase_with_gis` `psql -c 'ALTER EXTENSION postgis UPDATEto "3.0.3";' -d mydatabase_with_gis` psql -c 'SELECT postgis_full_version();' -d `mydatabase_with_gis`
11 -> 12	`dnf install postgresql12-server postgresql12-contrib postgis31_12`	`/usr/pgsql-12/bin/postgresql-12-setup initdb`	`cd /tmp` `su postgres` `/usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-11/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/11/data/ --new-datadir=/var/lib/pgsql/12/data/ --check` ... Clusters are compatible exit	`systemctl stop postgresql-11.service`	`cd /tmp` `su postgres` `/usr/pgsql-12/bin/pg_upgrade --old-bindir=/usr/pgsql-11/bin/ --new-bindir=/usr/pgsql-12/bin/ --old-datadir=/var/lib/pgsql/11/data/ --new-datadir=/var/lib/pgsql/12/data/` ... Upgrade Complete exit	`cp /var/lib/pgsql/11/data/postgresql.conf/var/lib/pgsql/12/data/postgresql.conf` `cp /var/lib/pgsql/11/data/pg_hba.conf/var/lib/pgsql/12/data/pg_hba.conf`	`systemctl start postgresql-12.service` `systemctl status postgresql-12.service`	`cd /tmp` `su postgres` `./analyze_new_cluster.sh` exit	`systemctl disable postgresql-11.service` `systemctl enable postgresql-12.service`	`su postgres` `psql -c 'ALTER EXTENSION postgis UPDATEto "3.1.2next";' -d mydatabase_with_gis` `psql -c 'ALTER EXTENSION postgis UPDATEto "3.1.2";' -d mydatabase_with_gis` psql -c 'SELECT postgis_full_version();' -d `mydatabase_with_gis`
12 -> 13

Problemes / Problems
- pg_upgrade
  - Your installation references loadable libraries that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file: loadable_libraries.txt
    - bash-4.2$ more loadable_libraries.txt
      could not load library "$libdir/rtpostgis-2.5": ERROR: could not access file "$libdir/rtpostgis-2.5": No such file or directory
    - Solució / Solution
      - check that you performed an update of postgis in all your databases
general (e.g. 9.5 with postgis 2.2 -> 9.6 with postgis 2.2)
1. install new postgresql version, with the same version for gis
  - Upgrade PostGIS will be done without upgrading the PostgreSQL version
  - CentOS PostgreSQL
    - CentOS 7
      - sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
      - sudo yum install postgresql96-server postgresql96-contrib postgis22_96
    - CentOS 8
      - ...
2. create dirs for new version (will create /var/lib/pgsql/9.6/)
  - sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
3. check upgrade
  - su postgres
  - /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/ --new-datadir=/var/lib/pgsql/9.6/data/ --check
    - ... *Clusters are compatible*
4. stop old service
  - sudo systemctl stop postgresql-9.5.service
5. run upgrade
  - su postgres
  - /usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/ --new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/ --new-datadir=/var/lib/pgsql/9.6/data/
  - Problems
    - pg_dump: [archiver (db)] query failed: ERROR: could not access file "$libdir/postgis-2.2": No such file or directory
      - check gis version in present database
    - cannot write to log file pg_upgrade_internal.log
      - make sure that you are on a writable dir before su postgres:
        
        cd /tmp
        
        su postgres
6. copy config files
  - sudo cp /var/lib/pgsql/9.5/data/postgresql.conf/var/lib/pgsql/9.6/data/postgresql.conf
  - sudo cp /var/lib/pgsql/9.5/data/pg_hba.conf/var/lib/pgsql/9.6/data/pg_hba.conf
7. start new service
  - sudo systemctl start postgresql-9.6.service
8. analyse cluster
  - su postgres
  - ./analyze_new_cluster.sh
9. disable old service (for next reboots)
  - sudo systemctl disable postgresql-9.5.service
10. enable new service (for next reboots)
  - sudo systemctl enable postgresql-9.6.service
9.2 -> 9.4
1. install new postgresql version
  - Linux downloads (Red Hat family)
    - sudo yum install https://download.postgresql.org/pub/repos/yum/...
  - sudo yum install postgresql94-server
  - /usr/pgsql-9.4/bin/postgresql94-setup initdb
2. install new pg_upgrade
  - sudo yum install postgresql94-contrib
3. check upgrade
  - sudo su postgres
  - /usr/pgsql-9.4/bin/pg_upgrade --check -b /usr/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.4/data
  - Problemes / Problems
    - FATAL: unrecognized configuration parameter "unix_socket_directory"
      - pg_upgrade unrecognized configuration parameter “unix_socket_directory”
        
        postgres --describe-config | grep unix_socket
    - Could not load library "$libdir/rtpostgis-2.0" (/usr/pgsql-9.4/lib/rtpostgis-2.0.so)
      - Missing libraries when upgrading to PostGIS 2.1 and PostgreSQL 9.3.1 using homebrew
      - st_asgml functions not dropped 2.0 to 2.1 upgrade #2489
      - Solució / Solution
        
        upgrade 9.2 from repo with postgis 2.0 to 9.2 from postgresql with postgis 2.1:
        
        sudo yum install -y https://download.postgresql.org/pub/repos/yum/9.2/redhat/rhel-7-x86_64/pgdg-centos92-9.2-3.noarch.rpm
        
        sudo yum install postgresql92-server postgis21_92
        
        sudo systemctl stop postgresql.service; sudo systemctl disable postgresql.service
        
        sudo systemctl start postgresql-9.2.service; sudo systemctl enable postgresql-9.2.service
        
        and then: pg_upgrade
    - Error: Package: postgis21_92-2.1.8-1.rhel7.x86_64 (pgdg92) Requires: SFCGAL
      - Solució / Solution
        
        Add repository @pgdg93:
4. stop old service
  - sudo systemctl stop postgresql.service
5. run upgrade
  - sudo su postgres
  - /usr/pgsql-9.4/bin/pg_upgrade -b /usr/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.4/data
6. copy config files
  - cp /var/lib/pgsql/data/postgresql.conf/var/lib/pgsql/9.4/data/postgresql.conf
  - cp /var/lib/pgsql/data/pg_hba.conf/var/lib/pgsql/9.4/data/pg_hba.conf
7. start new service
  - sudo systemctl start postgresql-9.4.service
  - sudo systemctl enable postgresql-9.4.service
8. analyze new cluster
  - ./analyze_new_cluster.sh

pg_dumpall
Replication

SQLite

sqlitebrowser
SQLite Manager (Firefox extension)

Integration with Django
Replicació / Replication
- Does SQLite support replication?
Limitacions / Limitations
- alter table drop column syntax error using sqlite
Dump to sql file
- sqlite3 sqlite.db .dump > toto.sql
Migration from sqlite to MySQL
- Using Django
  - python manage.py dumpdata
- Quick easy way to migrate SQLite3 to MySQL?
  - answer 7747146: sqlfairy, SQL-Translator
  - answer 6410922
- using SQL-Translator
  - Instal·lació
    - Mageia
      - urpmi perl-SQL-Translator perl-DBD-SQLite perl-Template-DBI
  - Usage
    - ...

Redis

Instal·lació / Installation
- Mageia
  - urpmi redis
Setup
- sudo systemctl start redis.service

InfluxDB

Influx Data
- Getting started
Instal·lació / Installation
- Downloads
  - rpm
    - wget https://dl.influxdata.com/influxdb/releases/influxdb-1.7.9.x86_64.rpm
    - Mageia
      - urpmi influxdb-1.7.9.x86_64.rpm
- Start
  - sudo systemctl start influxdb.service
- Docker
  - Official repository: influxdata / influxdata-docker
  - docker pull influxdb
  - How To Install InfluxDB Telegraf and Grafana on Docker
    1. Installing InfluxDB 1.7.x on Docker
      - Create new system user:
        
        sudo useradd -rs /bin/false influxdb
        
        user_group=$(cat /etc/passwd | awk -F: '/^influxdb/ {print $3":"$4}')
        
        echo ${user_group}
      - Create /etc/influxdb/influxdb.conf (owned by root.root)
        
        sudo mkdir -p /etc/influxdb
        
        docker run --rm influxdb influxd config | sudo tee /etc/influxdb/influxdb.conf > /dev/null
        
        ls -l /etc/influxdb/influxdb.conf
      - Create dir /var/lib/influxdb (owned by influxdb.influxdb)
        
        sudo mkdir -p /var/lib/influxdb
        
        sudo chown influxdb:influxdb -R /var/lib/influxdb
      - (optional) Create /etc/influxdb/scripts/influxdb-init.iql (owned by root.root)
        
        sudo mkdir -p /etc/influxdb/scripts
        
        cd /etc/influxdb/scripts
        
        sudo nano influxdb-init.iql
        
        CREATE DATABASE weather; CREATE RETENTION POLICY one_week ON weather DURATION 168h REPLICATION 1 DEFAULT;
      - (optional) Init database with created init file and create a user (--rm option must be specified before --user) (--user option is needed because files in /var/lib/influxdb must be owned by influxdb.influxdb):
        
        docker run --rm --user ${user_group} -e INFLUXDB_HTTP_AUTH_ENABLED=true \ -e INFLUXDB_ADMIN_USER=admin \ -e INFLUXDB_ADMIN_PASSWORD=admin123 \ -v /var/lib/influxdb:/var/lib/influxdb \ -v /etc/influxdb/scripts:/docker-entrypoint-initdb.d \ influxdb /init-influxdb.sh
        
        ls -l /var/lib/influxdb
      - Check that no other process is using port 8086:
        
        sudo netstat -tulpn | grep 8086
      - Check that no firewall is blocking port 8086
      - Start influxdb service with the right user:
        
        docker run -d -p 8086:8086 --user ${user_group} --name=influxdb \ -v /etc/influxdb/influxdb.conf:/etc/influxdb/influxdb.conf \ -v /var/lib/influxdb:/var/lib/influxdb \ influxdb \ -config /etc/influxdb/influxdb.conf
      - Check that influx is working:
        
        sudo netstat -tulpn | grep 8086
        
        curl -G http://localhost:8086/query --data-urlencode "q=SHOW DATABASES"
    2. Enabling authentication on InfluxDB for Docker
      - List containers
        
        docker container ls
      - Execute bash in container and connect to database
        
        docker exec -it influxdb /bin/bash
        
        influx
        
        SHOW DATABASES
        
        SHOW USERS
        
        if you did not create an admin user, do it now:
        
        CREATE USER admin WITH PASSWORD 'admin123' WITH ALL PRIVILEGES
        
        SHOW USERS
      - Modify conf file to enable http authentication (credentials will be needed):
        
        sudo nano /etc/influxdb/influxdb.conf
        
        [http]
        enabled = true
        bind-address = ":8086"
        auth-enabled = true
      - Restart container:
        
        docker restart influxdb
      - Connect to database using credentials (bash)
        
        docker exec -it influxdb /bin/bash
        
        influx -username admin -password admin123
        
        SHOW DATABASES
      - Connect to database using credentials (curl)
        
        curl -G -u admin:admin123 http://localhost:8086/query --data-urlencode "q=SHOW DATABASES"
    3. Installing Telegraf on Docker
      - Create new user
        
        sudo useradd -rs /bin/false telegraf
        
        telegraf_user_group=$(getent passwd | awk -F: '/^influxdb/ {print $3":"$4}')
      - Create config dir
        
        sudo mkdir -p /etc/telegraf
      - Create config file
        
        docker run --rm telegraf telegraf config | sudo tee /etc/telegraf/telegraf.conf > /dev/null
      - Set permissions
        
        sudo chown telegraf:telegraf /etc/telegraf/*
      - Modify telegram config file
        
        sudo nano /etc/telegraf/telegraf.conf
        
        [[outputs.influxdb]] ## HTTP Basic Auth username = "admin" password = "admin123"
      - Run telegraf (same network as influxdb container; /proc points to host)
        
        docker run -d --user ${telegraf_user_group} --name=telegraf \ --net=container:influxdb \ -e HOST_PROC=/host/proc \ -v /proc:/host/proc:ro \ -v /etc/telegraf/telegraf.conf:/etc/telegraf/telegraf.conf:ro \ telegraf
      - Check logs
        
        docker container logs -f --since 10m telegraf
      - Check data in influxdb
        
        docker exec -it influxdb influx -username admin -password admin123
        
        SHOW DATABASES
        
        USE telegraf
        
        SELECT * FROM cpu WHERE time < now() - 1m
    4. Visualizing Telegraf metrics in Grafana
      - Run grafana
        
        docker run -d --name=grafana -p 3000:3000 grafana/grafana
      - Check that grafana is running on port 3000:
        
        netstat -tulpn | grep 3000
      - Connect to web interface
        
        http://localhost:3000/
        
        admin/admin
        
        Add data source
        
        URL: ("IPv4Address" from docker network inspect bridge | grep influxdb -A 5)
        
        Basic Auth: enable
        
        Basic Auth Details
        
        User: admin
        
        Password: admin123
        
        InfluxDB Details
        
        Database: telegraf
        
        User: admin
        
        Password admin123
        
        HTTP Method: GET
        
        + -> Create -> Import
        
        Telegraf Host Metrics (1443)
      - ...
  - docker
    - docker run -d --name container-influxdb influxdb
  - docker compose (automatically create a database "iot")
    - script init-influxdb.sh will be executed using the following environment variables:
      - INFLUXDB_HTTP_AUTH_ENABLED
      - INFLUXDB_META_DIR
      - INFLUXDB_ADMIN_USER
      - INFLUXDB_ADMIN_PASSWORD (INFLUXDB_INIT_PWD)
      - INFLUXDB_USER
      - INFLUXDB_USER_PASSWORD
      - INFLUXDB_READ_USER
      - INFLUXDB_READ_USER_PASSWORD
      - INFLUXDB_DB (PRE_CREATE_DB)
    - docker-compose.yml
      - version: '3'
        
        services:
        influxdb:
            image: influxdb
            container_name: container-influxdb
            restart: always
            environment:
             - INFLUXDB_USER=telegraf     - INFLUXDB_USER_PASSWORD=telegraf
             - INFLUXDB_DB=sensors
            ports:
             - "8083:8083"
             - "8086:8086"
            volumes:
             - ~/docker/rpi-influxdb/data:/data
    - docker-compose up
Structure:
- time series
  - point: <measurement>[,<tag-key>=<tag-value>...] <field-key>=<field-value>[,<field2-key>=<field2-value>...] [unix-nano-timestamp]
    - Line protocol syntax
    - measurement is like a table
    - examples:
      - temperature,machine=unit42,type=assembly external=25,internal=37 1434067467000000000
      - ...
Connect to database
- influx -precision rfc3339
- server is running on a docker container:
  - docker exec -it container-influxdb influx-precision rfc3339
- List databases:
  - SHOW DATABASES
- Create data:
  - CREATE DATABASE home_db
  - USE home_db
  - INSERT temperature,machine=unit42,type=assembly external=25,internal=37
- Retrieve data:
  - SELECT * FROM temperature
  - SELECT mean("external") FROM "temperature" WHERE ("machine" = 'unit42') AND time >= now() - 30m GROUP BY time(2s) fill(null)

Telegraf

Telegraf
- Documentation
  - input plugins
  - output plugins
Instal·lació / Installation
- Download
  - rpm
    - wget https://dl.influxdata.com/telegraf/releases/telegraf-1.13.1-1.x86_64.rpm
    - Mageia
      - urpmi telegraf-1.13.1-1.x86_64.rpm
Configuració / Setup
- creeu un fitxer amb la configuració per defecte / create a file with default configuration:
  - telegraf config >telegraf.conf
- o bé feu servir / or use /etc/telegraf/telegraf.conf
- modifiqueu-lo, per exemple per a configurar / modify it, e.g. to setup MQTT Mosquitto -> InfluxDB
  - [[inputs.mqtt_consumer]] servers = ["tcp://raspberry_pi_ip:1883"] topics = [ "sensors" ] data_format = "influx"
  - [[outputs.influxdb]] urls = ["http://raspberry_pi_ip:8086"] database = "sensors" skip_database_creation = true username = "telegraf" password = "telegraf"

http://www.francescpinyol.cat/bd.html
Primera versió: / First version:
Darrera modificació: 22 d'agost de 2024 / Last update: 22nd August 2024

Cap a casa / Back home