Ri Xu Online

Wiki

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

Install PostgreSQL on OS X

$ brew install postgresql

Now run this command to finish creating the datbabase:

$ initdb /usr/local/var/postgres

Now run the following commands to start Postgres at login, so we already have Postgres running on the background:

$ mkdir -p ~/Library/LaunchAgents
$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

If this is an upgrade and you already have the org.postgresql.postgres.plist loaded:

$ launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
$ cp /usr/local/Cellar/postgresql/9.0.1/org.postgresql.postgres.plist ~/Library/LaunchAgents
$ launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

Or start manually with:

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

And stop with:

$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

Remove all rows from a table:

>> Truncate Table <tablename>

Import dump/sql file into PostgreSQL database

$ psql -h <user@ip_address_of_server> -U <database_username> -d <name_of_the_database> -f local/path/to/your/file.sql

HomeBrew Services

First, install brew services by tapping gapple/services (one time):

Here's an example usage

$ brew services restart postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

Behind the scenes, brew services start is doing everything in the post-install message above. It runs launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist. It Just Works. Now let's see everything we've loaded:

$ brew services list

Install PDO PostgreSQL

$ brew install php55-pdo-pgsql

Config Laravel

Open file: app/config/database.php, in the array replace 'default' => 'mysql', with 'default' => 'pgsql', now with the information from before edit the 'connections' array like so:

'connections' => array(
    'pgsql' => array(
        'driver'    => 'pgsql',
        'host'      => 'localhost',
        'database'  => 'your_database_name',
        'username'  => '',
        'password'  => '',
        'charset'   => 'utf8',
        'prefix'    => '',
        'schema'    => 'public'
    ),
)

After save file, should now have a functioning database that Laravel can talk to. We can use PG Commander or other GUI client to working with PostgreSQL databases.

Backup & Restore Database

Backup database

$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -Fc -b -v -f db.backup db_name

Backup table

$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -Fp -d db_name -t table_name > table.backup

Restore database

$ pg_restore -h 127.0.0.1 -p 5432 -U postgres -d db_name -v db.backup

Restore table

$ psql -h 127.0.0.1 -p 5432 -U postgres -d db_nmae < table.backup

Copy in Action

Extracting table to a csv delimited file:

\copy (SELECT * FROM table_name) TO '~/file_name.csv' WITH (FORMAT CSV);

And for loading data into a table the equivalent for each of the above:

\copy table_name FROM '~/file_name.csv' WITH CSV;

Run pg_dump --help to get more helpful information.

Log Analyzer

pgBadger is a PostgreSQL log analyzer with fully detailed reports and graphs.

Installation

$ brew install pgbadger

If the log file is CSV format, we need install Text::CSV_XS module:

$ perl -MCPAN -e shell
install Text::CSV_XS

Analyze single file:

$ pgbadger -f csv postgresql-2015-04-06_121457.csv

Analyze a log of time periods

$ pgbadger -b "2015-04-05 12:00:00" -e "2015-04-06 12:00:00" /var/pg_log/* -o /tmp/tmp_pgbadger.html