|
1 2 3 4 5 6 7 8 9 |
# 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 |
Category Archives: postgres
Postgres – Export a Table to CVS File
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# 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 (SELECT * FROM mytable) TO E'/home/a/data/export.csv' CSV DELIMITER ',';" # Header psql -h 127.0.0.1 -p 5432 -U database_user -d database_name -c "COPY (select column_a,column_b from mytable limit 10) TO STDOUT WITH CSV HEADER;" > home/a/data/export.csv |
pgadmin3 – installation – latest version
Version 1.14 - OLD stable version
1.18 and ... Read more
|
1 |
sudo apt-get install -y pgadmin3 |
Postgres – Tips and Tricks
Postgres – Executing SQL-Statement in several remote machines sequentially
|
1 |
echo 'psql -a -d mydatabase -p 5432 -c "\d mytable;"'| ssh myuser@myserverhost.net 'sudo su - postgres -c "bash -x -- " ' |
|
1 |
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
|
1 2 3 |
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
Configuration
Example ... Read more
|
1 |
sudo apt-get install -y pgbouncer |
Postgres – How to Dump, Restore, Create and Drop Databases
1. Postgres - Dump
Example
Dumped ... Read more
|
1 2 3 |
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 |
|
1 2 3 |
pg_dump -p 5432 -U postgres --verbose --inserts \ --format=plain --encoding=utf8 --verbose \ --file postgres.$(date +"%Y%m%d_%H%M").sql postgres |
MySQL – Dump and Restore a Database
1. MySQL - Dump
Compress SQL Dump(Optional):
2. ... Read more
|
1 |
mysqldump -h HOSTNAME -u USERNAME --password=PASSWORD DBNAME > dbdump.sql |
|
1 |
bzip2 dbdump.sql |
Postgres – Hamming distance in plpython
|
1 2 3 4 5 6 7 8 |
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; |