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

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:
      • 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
        • 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 geodjango --command "CREATE EXTENSION postgis;"
  • 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"
          • /usr/pgsql-${pgsql_version}/bin/psql -f my_cluster.sql postgres
        • pg_recover
  • Upgrade
    • 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
          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ó: 22 de juny de 2017 / Last update: 22nd June 2017

Valid HTML 4.01!

Cap a casa / Back home