Category: postgres
-
Ubuntu – Postgres 9.4 Installation in Ubuntu 14.04
# 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
# 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
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…
-
Postgres – Tips and Tricks
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
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
CREATE LANGUAGE plperlu; create or replace function sleep(integer) returns integer as ‘return sleep(shift)’ language plperlu;
-
pgBouncer – Installation and Configuration in Ubuntu
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
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
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
-
Postgres – Hamming distance in plpython
CREATE OR REPLACE FUNCTION util.hamming_distance (s1 text, s2 text) RETURNS integer /* select * from util.hamming_distance (‘hella3’, ‘hillo2’) */ AS $$ return sum([ch1 != ch2 for ch1, ch2 in zip(s1, s2)]) $$ LANGUAGE plpythonu;