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





 
our entire universe; minuscule spec in gigantic multiverse which is mostly lethal.



 
"Mediocrity knows nothing higher than itself; but talent instantly recognizes genius.”
- Sir Arthur Conan Doyle
$$e = \sum_{n=0}^\infty \frac{1}{n!}$$

PostgreSQL

Sunday, 09 March 2014 18:14

PostgreSQL

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
DevAdmin: FOREIGN TABLES
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 SEQUENCE data_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
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

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