|
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: database
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 |
WordPress – Simple Blog Installation in Ubuntu – PHP, Apache, MySQL
Installation
References
1. Home : http://wordpress.org/
1. ... Read more
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
#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 # create download directory mkdir -p ~/build/wordpress cd ~/build/wordpress # download wget http://wordpress.org/latest.zip unzip -qo latest.zip cd wordpress # create .htaccess file for nice URL cat > .htaccess <<"_EOF_" # BEGIN WordPress <IfModule mod_rewrite.c> RewriteEngine On RewriteBase / RewriteRule ^index\.php$ - [L] RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule . /index.php [L] </IfModule> # END WordPress _EOF_ # check .htaccess contents #cat ./wordpress/.htaccess #ls -l /var/www/ # create wp config file cp wp-config-sample.php wp-config.php #database credentials wp_database="wpzdb" wp_user="wpz" wp_password="wpzpassword" # create user - inline in bash echo " CREATE DATABASE IF NOT EXISTS $wp_database; CREATE USER '$wp_user'@'localhost' IDENTIFIED BY '$wp_password'; CREATE USER '$wp_user'@'127.0.0.1' IDENTIFIED BY '$wp_password'; GRANT ALL ON $wp_database.* TO '$wp_user'@'localhost' IDENTIFIED BY '$wp_password'; GRANT ALL ON $wp_database.* TO '$wp_user'@'127.0.0.1' IDENTIFIED BY '$wp_password'; "|mysql -u root --password=root #set database credentials in wp-config.php sed -i "s/define('DB_NAME', 'database_name_here');/define('DB_NAME', '$wp_database');/ig" wp-config.php sed -i "s/define('DB_USER', 'username_here');/define('DB_USER', '$wp_user');/ig" wp-config.php sed -i "s/define('DB_PASSWORD', 'password_here');/define('DB_PASSWORD', '$wp_password');/ig" wp-config.php #copy wordpress files to the /var/www directory cd .. sudo cp -r wordpress/* /var/www/ sudo chown www-data:www-data -R /var/www # Go to http://localhost to finish up installation #in case of errors, check if the apache webserver and mysql are running ps -ef|grep -E "apache2|mysql" |
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; |
MySQL – Append text data in column
|
1 2 3 |
UPDATE mytable SET mycolumn = CONCAT(mycolumn, 'this is my appended text') where id=1234; |
MySQL – Sorting records/users based on closeness to a age in SQL
Use: sort by users whose age is closest to a defined age. In the example below, 25 ... Read more