Bases de dades / Databases

Índex / Index

General



W3-mSQL PHP3 (es) (ch) JDBC

Info GUI
 
miniSQL    
MySQL
 
PostgreSQL  

SQL
  • Comparison of relational database management systems
  • Firebird
  • MariaDB (fork from MySQL) (SkySQL) (wp)
  • MySQL
  • 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

    1. (create users)
    2. createdb [-T template0] my_db
    3. psql [--set ON_ERROR_STOP=on] [--single-transaction] my_db <my_db.sql
    4. 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
    • 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.
    • 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)
      • 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)

Replicació / Replication

Cerca / Search

Disseny / Design

AWS

MongoDB

MySQL / MariaDB

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-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:
          • ...
        • Problemes / Problems
          • postgres=# SELECT pg_stat_statements_reset();
            ERROR:  function pg_stat_statements_reset() does not exist
    • 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
        • Mageia <6
          • urpmi postgis
      • 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
      • 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)
        • pg_dump
        • pg_restore
        • Performance
  • Upgrade
    • 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
              • 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
          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

SQLite

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

InfluxDB
  • Influx Data
  • 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
          • ...
      • 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
  • 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ó: 1 de març de 2024 / Last update: 1st March 2024

Valid HTML 4.01!

Cap a casa / Back home