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 <[email protected]_address_of_server> -U <database_username> -d <name_of_the_database> -f local/path/to/your/file.sql
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
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
$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -F c -b -v -f "~/db.backup" db_name
$ pg_restore -h 127.0.0.1 -p 5432 -U postgres -d db_name -v "~/db.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;
pg_dump --help to get more helpful information.