Tutorial on installing PostgreSQL on Mac OS, ospostgresql

Tutorial on installing PostgreSQL on Mac OS, ospostgresql

Let me start with it. For a long time, I have been cooperating with MySQL, a long-tested old friend. However, after learning a little about PostgreSQL, I have been fascinated by its rich features. For example, the field type supports json, xml, and array native. Compared with MySQL, PostgreSQL is more advanced.

Install brew

Official documentation:
Http://mxcl.github.com/homebrew/
First install Git and open a shell:

cd /usr/localsudo mkdir homebrewcurl -L https://github.com/mxcl/homebrew/tarball/master | sudo tar xz --strip 1 -C homebrewcd homebrew/bin./brew -vfile brewcat brew | moresudo ./brew update

If an error occurs while executing the "brew update" command, make sure that the owner permission of the folder/usr/local is yours rather than root:

sudo chown $USER /usr/localbrew updat

Update PATH configuration in ". bash_profile"
(If ~ There is no file ". bash_profile" under. Execute: touch '. bash_profile ')

vim '.bash_profile'

Join

export PATH=$PATH:/usr/local/homebrew/bin

Then run brew directly (No./brew)
If you have Git, you can install it like this (not tested)

git clone https://github.com/mxcl/homebrew.gitcd homebrew/bincd homebrew/bin./brew -v

Installation test

./brew install wget./brew uninstall wget./brew searc /apache*/

Install PostgreSQL

Use brew.

brew install postgres

Initialize Database

initdb /usr/local/var/postgres

Start or stop a database

Start the service:

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

Stop Service:

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

If it is too troublesome, you can add it to boot automatically

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents

Common Operations

1. Create a PostgreSQL user

createuser username -P#Enter password for new role:#Enter it again:

The above username is the user name. Press enter to enter the user password twice, and the user is created. For more information about creating multiple users, see "createuser -- help.

2. Create a database

createdb dbname -O username -E UTF8 -e

We created a database named dbname and specified username as the owner of the database. The database encoding (encoding) is UTF8, the "-e" parameter is used to display the commands for database operations.

For more information about database creation, see "createdb -- help.

3. Connect to the database

psql -U username -d dbname -h 127.0.0.1

Experience

While still learning, I think it is not much different from MySQL. They all Execute standard SQL statements. The limit statement is different from MySQL. For example, in MySQL, obtain the first 10 records:

select * from table limit 0, 10

In PostgreSQL, it is:

select * from table limit 10 offset 0

In this way, PostgreSQL looks clearer. The offset in MySQL is optional and can be used later. At the beginning, it is easy to tell the positions of the offset and limit values.