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