Bases de dades / Databases

• 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
  		delete a user			dropuser
  	database	create a database			createdb --username my_user my_db
  		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 postgres cat 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	\?
  		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)
    • SQL cheat sheet
    • SQL Subquery
    • SQL Correlated Subquery
  • 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 Databases
• NoSQL (wp)
• Implementacions / Implementations
  • BigTable (Google) (wp)
  • Cassandra (Facebook) (wp)
  • CouchDB
  • MongoDB (wp)
  • Redis

• 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)

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

• Open source database design tool [closed]
• Eclipse
  • DB Schema Viewer
  • ER diagram editor for eclipse
• DBDesigner 4
• Open System Architect
• Jailer

• AWS database
  • AWS RDS
  • AWS Aurora

• MongoDB (wp)
• Integration with Django
• Javascript
• MongoDB replication (replica set)
• Admin UIs

• 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"
    • Django does not correctly close MySQL connections #177
    • Django mysql too many connections
    • Using Django ORM in threads and avoiding “too many clients” exception by using BoundedSemaphore
    • In Django, I get the "too many connections error." How do I solve this problem, and what causes it?

• Technet
  • Oracle8i SQL Reference
• Error messages

• Instal·lació / Installation
  • Mageia
    • urpmi postgresql9.4-server
    • systemctl enable postgresql.service
    • systemctl start postgresql.service
  • AlmaLinux
  • Installation from PostgreSQL.org repos
  • only client (psql)
  • sudo yum install postgresql15
  • /usr/pgsql-15/bin/psql
  • /bin/psql
    
  • server
  • sudo yum install postgresql15-server
  • 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-server postgresql94-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
    	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 [-h db_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
        • sudo dnf install postgis
      • 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
      • dnf install postgis34_16
      • Problemes / Problems
        • OSError: /usr/gdal38/lib/libgdal.so.34: undefined symbol: proj_crs_has_point_motion_operation
          • Thread: postgis/gdal - undefined symbol: proj_crs_has_point_motion_operation
          • postgis 3.4.2-3 build for RHEL9 postgis_raster plugin crashes
          • Solution:
            • remove proj92 and reinstall postgis:
              • dnf remove proj92
              • dnf install postgis34_16
            • downgrade from 3.4.2 to 3.4.1:
              • dnf downgrade postgis34_16-3.4.1-1PGDG.rhel8
    • 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
        • sudo dnf install postgis
  • 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
    • Upgrade PostGIS in AWS RDS
    • 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 # update psql -c 'ALTER EXTENSION postgis UPDATE;' -d mydatabase_with_gis psql -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 # update psql -c 'ALTER EXTENSION postgis UPDATE;' -d mydatabase_with_gis psql -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 -f my_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 AWS RDS
  • Upgrade PostGIS
  • Upgrading a PostgreSQL Cluster (9.4). Use one of the following methods:
    
    1. 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 UPDATE to "3.0.3next";' -d mydatabase_with_gis psql -c 'ALTER EXTENSION postgis UPDATE to "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 UPDATE to "3.1.2next";' -d mydatabase_with_gis psql -c 'ALTER EXTENSION postgis UPDATE to "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
                  1. 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
                  2. 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
    2. pg_dumpall
    3. Replication

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

• Influx Data
  • Getting started
• Info
  • Projectes Raspberry Pi amb MQTT / InfluxDB / Grafana
• 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)

• Info
  • Projectes Raspberry Pi amb MQTT / InfluxDB / Grafana
• 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ó: 15 de febrer de 2026 / Last update: 15th February 2026