Gugu Ncube

, , , ,

Category: database

  • Ubuntu – Postgres 9.4 Installation in Ubuntu 14.04

    —

    by

    Gugu Ncube
    in , , , ,

    # remove the old postgres version (not data) sudo apt-get remove -y postgresql postgresql-9.3 # add package sources list, key and update sudo sh -c “echo ‘deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main’ > /etc/apt/sources.list.d/pgdg.list” wget –quiet -O – http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add – sudo apt-get update -y –fix-missing sudo apt-get install -y libpq-dev postgresql-9.4 References 1.…

  • Postgres – Export a Table to CVS File

    —

    by

    Gugu Ncube
    in ,

    # In the postgres console or pgAdmin #-h: host #-p: port #-U: database user #-d: database name psql -h 127.0.0.1 -p 5432 -U database_user -d database_name \COPY (SELECT * FROM mytable) TO E’/home/a/data/export.csv’ CSV DELIMITER ‘,’; One Liner from the command line # CSV psql -h 127.0.0.1 -p 5432 -U database_user -d database_name -c “COPY…

  • pgadmin3 – installation – latest version

    —

    by

    Gugu Ncube
    in ,

    Version 1.14 – OLD stable version sudo apt-get install -y pgadmin3 1.18 and latest version The latest version is available from the pgadmin git server. Only disadvantage: it has to be build from scatch. Depending on your machine capabilities, this can take several minutes. # Prepare sudo apt-get install -y build-essential automake libwxgtk2.8-dev libwxgtk2.8-dbg libxml2-dev…

  • WordPress – Simple Blog Installation in Ubuntu – PHP, Apache, MySQL

    —

    by

    Gugu Ncube
    in , ,

    Installation #php sudo apt-get install -y php5 #php5 with apache2 sudo apt-get install -y libapache2-mod-php5 #optional: execute standalone php scripts sudo apt-get install -y php5-cli #mysql driver for PHP5 sudo apt-get install -y php5-mysql #unzip sudo apt-get install -y unzip # mysql sudo apt-get install -y mysql # apache webserver sudo apt-get install -y apache2…

  • Postgres – Tips and Tricks

    —

    by

    Gugu Ncube
    in ,

    Reload pg_ctl -D . reload Stop – soft pg_ctl -D . stop -m i Stop – force pg_ctl -D . stop -m f Start pg_ctl -D . start /usr/bin/postmaster -p 5432 -D /dev/postgres/pgsql/data > /dev/postgres/pgsql/postmaster_logfile 2>&1& Restart pg_ctl -D . restart Restart to particular port pg_ctl -D . restart -m f -o ‘-p 5435’ PG_DUMP…

  • Postgres – Executing SQL-Statement in several remote machines sequentially

    —

    by

    Gugu Ncube
    in , , , , , ,

    echo ‘psql -a -d mydatabase -p 5432 -c “\d mytable;”‘| ssh myuser@myserverhost.net ‘sudo su – postgres -c “bash -x — ” ‘ echo ‘psql -a -d mydatabase -p 5432 -c select id from mytable where id = 393050;”‘| ssh myuser@myserverhost.net ‘sudo su – postgres -c “bash -x — ” ‘

  • Postgres – SLEEP Function

    —

    by

    Gugu Ncube
    in ,

    CREATE LANGUAGE plperlu; create or replace function sleep(integer) returns integer as ‘return sleep(shift)’ language plperlu;

  • pgBouncer – Installation and Configuration in Ubuntu

    —

    by

    Gugu Ncube
    in ,

    PgBouncer is a great lightweight connection pool for Postgres Installation sudo apt-get install -y pgbouncer Configuration Example pgbouncer.ini configuration file postgres = host=127.0.0.1 port=5432 dbname=postgres postgres = host=127.0.0.1 dbname=postgres postgres = host=127.0.0.1 port=5432 user=jon password=doe client_encoding=UNICODE datestyle=ISO connect_query=’SELECT 1′ logfile = pgbouncer.log pidfile = pgbouncer.pid listen_addr = * listen_port = 6000 unix_socket_dir = /tmp auth_type…

  • Postgres – How to Dump, Restore, Create and Drop Databases

    —

    by

    Gugu Ncube
    in ,

    1. Postgres – Dump pg_dump -p 5432 -U postgres –verbose –inserts \ –format=plain –encoding=utf8 –verbose \ –file DB_NAME.$(date +”%Y%m%d_%H%M”).sql DB_NAME Example pg_dump -p 5432 -U postgres –verbose –inserts \ –format=plain –encoding=utf8 –verbose \ –file postgres.$(date +”%Y%m%d_%H%M”).sql postgres Dumped File: postgres.20130114_1045.sql Note: –inserts and –format=plain is not the fastest option for dumping databases and often leads…

  • MySQL – Dump and Restore a Database

    —

    by

    Gugu Ncube
    in ,

    1. MySQL – Dump mysqldump -h HOSTNAME -u USERNAME –password=PASSWORD DBNAME > dbdump.sql Compress SQL Dump(Optional): bzip2 dbdump.sql 2. MySQL – Restore Decompress SQL Dump(Optional): bzip2 -d dbdump.sql.bz2 mysql -h HOSTNAME -u USERNAME –password=PASSWORD DBNAME < dbdump.sql