PostgreSQL#

PostgreSQL 9.2 on Ubuntu 12.04 (source):

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Allow connections via socket and MD5 password#

sudo vim /etc/postgresql/9.2/main/pg_hba.conf:

# md5-based auth instead of peer auth
#local   all             all                                     peer
local   all             all                                     md5

Reload server:

service postgresql reload

Allow User Access as postgres#

This should work (as normal user):

cat <<EOF >> ~/.pgpass
# hostname:port:database:username:password
localhost:*:postgres:postgres:postgres_password
EOF
psql postgres postgres

But it does not by default. Set password for postgres:

sudo -Hnu postgres -- psql -c "alter user postgres with password 'postgres_password'"

Allow local login:

cat <<EOF >> /etc/postgresql/9.3/main/pg_hba.conf
local   all             all                                     md5
EOF

Note

This will not work if you leave the line:

local   all             postgres                                peer

Use .pgpass of postgres user, so sudo -Hnu postgres -- psql still works:

cat <<EOF >> /var/lib/postgresql/.pgpass
# hostname:port:database:username:password
localhost:*:postgres:postgres:postgres_password
EOF

Test:

psql postgres postgres -c "SELECT 'world' AS hello"
sudo -Hnu postgres -- psql -c "SELECT 'world' AS hello"

Example database#

Connect as user postgres (admin):

sudo -u postgres psql

Create user and database:

CREATE USER sample WITH PASSWORD 'sample';
CREATE DATABASE users WITH OWNER sample;
GRANT ALL PRIVILEGES ON DATABASE users TO sample;

Add line to your .pgpass file:

echo '# hostname:port:database:username:password' >> ~/.pgpass
echo '*:*:users:sample:sample' >> ~/.pgpass
cat ~/.pgpass

Try it:

psql users sample

Todo

publish repo with example db, link here

Clean up:

sudo -u postgres psql <<'EOF'
DROP DATABASE users;
DROP USER sample;
EOF

Clean Your Room!#

sudo -Hu postgres vacuumdb -az

Create/Drop User and DB via CLI Tools#

Create:

sudo -Hnu postgres createuser foo --encrypted --createdb --pwprompt
echo 'localhost:*:*:foo:<pass_here>' >> ~/.pgpass
vim ~/.pgpass
sudo -Hnu postgres createdb --owner foo foo

Drop:

sudo -Hnu postgres dropdb foo
sudo -Hnu postgres dropuser foo

Text Similarity#

Install and enable:

# check version
sudo -iu postgres psql -c "select version()"
wajig install postgresql-contrib-9.4
sudo -iu postgres psql -c "create extension pg_trgm" DBNAME

Usage:

SELECT similarity('foo bar baz', 'foo bar baz qux');

Docs: http://www.postgresql.org/docs/9.1/static/pgtrgm.html

Minor Version Upgrade#

Here’s the log from 9.4 to 9.5:

wajig install postgresql-9.5
# backups
cp -r /var/lib/postgresql/9.4/main/ /var/lib/postgresql/9.4/main.bak
cp -r /var/lib/postgresql/9.5/main/ /var/lib/postgresql/9.5/main.bak

service postgresql stop
pg_dropcluster 9.5 main
pg_upgradecluster 9.4 main

# verify stuff

# remove old stuff
pg_dropcluster 9.4 main
wajig purge postgresql-9.4

# verify again

Minimal Upsert Example (9.5)#

CREATE TEMP TABLE tmp_target (
  key   TEXT UNIQUE,
  value TEXT
)
;

INSERT INTO tmp_target VALUES
  ('foo', 'Foo'),
  ('bar', 'Bar')
;

CREATE TEMP TABLE tmp_source (
  key   TEXT UNIQUE,
  value TEXT
)
;

TRUNCATE tmp_source;
INSERT INTO tmp_source (key, value) VALUES
  ('foo', 'UPDATED Foo'),
  ('bar', 'Bar'),
  ('baz', 'Baz')
;

INSERT INTO tmp_target (key, value)
    SELECT key, value FROM tmp_source
ON CONFLICT (key)
  DO UPDATE SET value = EXCLUDED.value -- http://stackoverflow.com/a/35159684
;

SELECT * FROM tmp_target;

DROP TABLE tmp_target;
DROP TABLE tmp_source;

Dump and Load Table via copy PSQL command#

-- dump to file
\copy foo TO '/tmp/foo.csv';

-- create load table based on source table
create table tmp_foo as select * from foo with no data;
\copy tmp_foo from '/tmp/foo.csv';

select * from tmp_foo;
select * from foo;

-- see Upsert above
drop table tmp_foo;
On this page