Dave Horner's Website - Yet another perspective on things...
Home Tech Talk Random Tech PostgreSQL
56 guests
Rough Hits : 2701549
moon and stars
how did u find my site?

onomastics (seen in code)

"How did I come to know what I know about the world and myself? What ought I to know? What would I like to know that I don't know? If I want to know about this or that, where can I get the clearest, best and latest information? And where did these other people about me get their ideas about things, which are sometimes so different from mine?" - H.G. Wells
appreciate the information? drop me a line, share, or +1.


Sunday, 09 March 2014 18:14


PostgreSQL: The world's most advanced open source database - the official homepage.
PostgreSQL: Manuals
PostgreSQL wiki
The Most Amazing PostgreSQL Database - IMHO
PostgreSQL vs. MS SQL Server - Why PostgreSQL is way, way better than MS SQL Server
Things to find out about when moving from MySQL to PostgreSQL - PostgreSQL wiki - PostgreSQL has many features MySQL doesn't.
Why Postgres | Craig Kerstiens Part 2 | Craig Kerstiens
Building DBD::Pg for perlbrew on Ubuntu - M.C. Pantz
sudo apt-get install libpq5 postgresql-server-dev-9.1
POSTGRES_HOME='/usr/lib/postgresql/9.1' POSTGRES_INCLUDE='/usr/include/postgresql' cpanm Bundle::DBD::Pg

quick how do I use it?

database - How to configure postgresql for the first time? - Stack Overflow - sudo -u postgres psql template1;ALTER USER postgres with encrypted password '';sudo -u postgres createuser -U postgres -d -e -E -l -P -r -s root;createdb ;psql template1
then psql /? and (/q or Ctrl+D) to exit.
PGDATA,PGPORT,PGHOST,PGPASSWORD,PGUSER are all env variables of interest.

posgresql password config

~.pgpass;localhost:5432:dbname:user:secret;chmod 0600 ~.pgpass;
runas /user:postgres "cmd";cd %appdata%;mkdir postgresql;cd postgresql;notepad pgpass.conf;

show me the databases

select * from pg_database; or \l from psql

show me the tables

SELECT * FROM pg_catalog.pg_tables
SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
show tables in postgresql - Stack Overflow

PostgreSQL Notify/Listen

HOWTO: Automatically Responding To PostgreSQL Table Changes With Twisted
AnyMQ::Pg - Use built-in PostgreSQL 'LISTEN' and 'NOTIFY' commands for message-passing. - metacpan.org
Listen/Notify improvements in PostgreSQL 9.0 | End Point Blog
BZERK Open Source - Executing perl code on PostgreSQL triggers.
ryandotsmith/queue_classic -QueueClassic Simple, efficient worker queue for Ruby & PostgreSQL isten/notify & row locking.
chanks/que - high-performance alternative to DelayedJob or QueueClassic that improves the reliability of your application by protecting your jobs with the same ACID guarantees as the rest of your data. primary goal is reliability, secondary goal is performance. Que Ruby Postgres with JSON datatype.

Load balance || connection pool || replication || parallel query

pgpool Wiki
DevAdmin: Pgpool Configuration & Failback

9.3 apt packages

Apt - PostgreSQL wiki BSD

Foreign Data Wrapper (FDW)

PostgreSQL is not just about the data in PostgreSQL.
installing the client couldn't be easier
apt-get install pgxnclient,yum install pgxnclient,pip install pgxnclient,easy_install pgxnclient

Foreign data wrappers - PostgreSQL wiki
Dave's Postgres Blog: PostgreSQL 9.1, meet MySQL
dpage/mysql_fdw - MySQL FDW for PostgreSQL 9.1+

Javascript in your PostgreSQL

9.3 gave us JSON datatypes, plv8 gives us procedures in javascript; Node.js in PostgreSQL, why yes.
plv8: A procedural language in JavaScript powered by V8 / PostgreSQL Extension Network
clkao/plv8x - Helpers for managing plv8 javascript modules
Using JavaScript in PostgreSQL | End Point Blog
PgREST: Node.js in the Database
poga/pgrest-schemaless - a schemaless database with firebase API on PgREST

Postgresql 9.3: Creating an index on a JSON attribute | Johnny Morano's Blog - indexing JSON document by key;
CREATE TABLE data ( id bigint DEFAULT nextval('data_seq'::regclass) NOT NULL, form_name TEXT, form_data JSON );
CREATE INDEX ON data USING btree (form_name, json_extract_path_text(form_data,'name'));

PostgreSQL and perl

my $dbh = DBI->connect("DBI:Pg:dbname=x;host=localhost", "user", "secret", {'RaiseError' => 1});
DBD::Pg - search.cpan.org
DBD::PgPP - search.cpan.org
Installing DBD::Pg with Strawberry Perl | Jawnsy's Journal on Life, Software and Engineering

Postgres games

gciolli/pgChess - pgxnclient install pgchess;pgxnclient install pg2podg;create extension pgchess;create extension pg2podg;
Relational Database Technologies: psql client can be a chess board now...


pgModeler - PostgreSQL Database Modeler
ER-Mapping with OpenShift, Getting Started with pgModeler | Openshift Blog

MusicBrainz Database - MusicBrainz - MusicBrainz Database is built on the PostgreSQL relational database engine and contains all of MusicBrainz' music metadata. This data includes information about artists, release groups, releases, recordings, works, and labels, as well as the many relationships between them.

< Prev  Next >
Last Updated on Saturday, 03 January 2015 01:31