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
    admin user


    root

    postgres
    default database




    postgres
    CLI

    user
    create a user

    • su - postgre
    • 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 (cuustom 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
    commands
    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

    users


    \du

    processes
    show processlist;



    max connections
    SHOW VARIABLES LIKE 'max_connections';


    SQL




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

No SQL (document-oriented)

Replicació / Replication

Cerca / Search

Disseny / Design

MongoDB

MySQL / MariaDB

Oracle

PostgreSQL

  • Installation
    • Mageia
      • urpmi postgresql9.4-server
      • systemctl enable postgresql.service
      • systemctl start postgresql.service
    • CentOS
      • 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,}')
  • 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.
              }
          }
    • 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
    • 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;"
          • 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/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;
  • PostGIS
    • GeoDjango
    • Installation
      • Mageia
        • Mageia <6
          • 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
      • 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 geodjango --command "CREATE EXTENSION postgis;"
    • Get version
      • \c my_database;
        SELECT postgis_full_version();
    • Upgrade
  • Backup
    • SQL Dump
      • Install
        • CentOS
          • sudo yum install postgresql96-contrib
          • sudo yum install holland-pgdump
      • Usage
        • pg_dump
        • pg_dumpall
          • $ 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"

        • pgsql (recover)
          • /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"
        • pg_recover
  • Upgrade
    • Upgrade PostGIS
    • Upgrading a PostgreSQL Cluster (9.4)
      1. pg_dumpall
      2. pg_upgrade (pg_migrator)
        • PostgreSQL upgrade on CentOS
        • 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
      3. Replication

SQLite

http://www.francescpinyol.cat/bd.html
Primera versió: / First version:
Darrera modificació: 18 d'abril de 2018 / Last update: 18th April 2018

Valid HTML 4.01!

Cap a casa / Back home