Installation and use of PostgreSQL under Mac

Source: Internet
Author: User
Tags locale postgresql psql postgresql version

Under Mac, you can use homebrew to install PostgreSQL directly:

Brew Install Postgresql-v

For a moment, PostgreSQL will be installed. Next is the initial database, execute the command at the terminal, and initially configure PostgreSQL:

initdb/usr/local/var/postgres-e UTF8

The above specifies that "/usr/local/var/postgres" is the configuration data for PostgreSQL directory, and setting the database data encoding is UTF8, more configuration information can be "Initdb--help" view.

Set to boot PostgreSQL:

Ln-sfv/usr/local/opt/postgresql/*. plist ~/library/launchagentslaunchctl Load ~/library/launchagents/ Homebrew.mxcl.postgresql.plist

Start PostgreSQL:

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

Turn off PostgreSQL:

pg_ctl-d/usr/local/var/postgres stop-s-M fast

Create a PostgreSQL user

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

The above username is the user name, enter the user password 2 times after the user creation is complete. More user-created information can be viewed "CreateUser--help".

Create a database

Createdb Dbname-o username-e utf8-e

The above creates a database named dbname, and specifies that username is the owner of the database (owner), the database Encoding (encoding) is UTF8, and the parameter "-e" means the command that performs the operation of the database is displayed.

More database creation information can be viewed "createdb--help".

Connecting to a database

127.0. 0.1

PostgreSQL Database Operations

To display the database you have created:

You can also view the list that has been created on the terminal without connecting to the PostgreSQL database:


Connecting to a database

\c dbname

Show Database Tables

To create a table named Test

int, text VARCHAR (());

Insert a record

INSERT into Test (ID, text) VALUES (1'sdfsfsfsdfsdfdf');

Query records


Update record

' aaaaaaaaaaaaa ' 1;

Delete the specified record


Delete a table


Deleting a database


or use the dropdb command to delete the database on the terminal

Dropdb-u User dbname

Here is the PHP operation class for your own PostgreSQL:

<? php
define ("HOST", "");
define ("PORT", 5432);
define ("DBNAME", "dbname");
define ("USER", "user");
define ("PASSWORD", "password");
class Ext_Pgsql {
    // singleton
    private static $ instance = null;
    private $ conn = null;
    private function __construct ()
        $ this-> conn = pg_connect ("host =". HOST. "port =". PORT. "dbname =". DBNAME. "user =". USER. "password =". PASSWORD) or die ('Connect Failed: '. pg_last_error ());
    public function __destruct ()
        @pg_close ($ this-> conn);
    / **
     * Singleton mode
     * @param $ name
     * /
    public static function getInstance ()
        if (! self :: $ instance)
            self :: $ instance = new self ();
        return self :: $ instance;
    / **
     * Get record
     * /
    public function fetchRow ($ sql)
        $ ret = array ();
        $ rs = pg_query ($ this-> conn, $ sql);
        $ ret = pg_fetch_all ($ rs);
        if (! is_array ($ ret))
            return array ();
        return $ ret;
    / **
     * Execution instructions
     * @param string $ sql
     * /
    public function query ($ sql)
        $ result = pg_query ($ this-> conn, $ sql);
        if (! $ result)
            die ("SQL: {$ sql}". pg_last_error ());
    / **
     * Get a record
     * /
    public function fetchOne ($ sql)
        $ ret = array ();
        $ rs = pg_query ($ this-> conn, $ sql);
        $ ret = pg_fetch_all ($ rs);
        if (! is_array ($ ret))
            return array ();
        return current ($ ret);

Some questions

Version 9.2 of PostgreSQL upgraded to 9.3. Data compatibility issues after version 1

Connect the PostgreSQL times following error:

psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

Open the Service log for PostgreSQL found to be the data compatibility issue with version 9.2 of PostgreSQL upgraded to 9.3.1:

tail -f /usr/local/var/postgres/server.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.1.

For version data upgrade issues, PostgreSQL provides pg_upgrade for data migration after version, using the following:

pg_upgrade -b old version bin directory -B new version bin directory -d old version data directory -D new version data directory [other options ...]

Before data migration, remember to close the PostgreSQL Postmaster service, or the following error will be reported:

There seems to be a postmaster servicing the new cluster.
Please shutdown that postmaster and try again.
Failure, exiting

Close postmaster with Pg_ctl:

pg_ctl-d/usr/local/var/postgres stop

This can also be turned off under Mac:

Launchctl Unload ~/library/launchagents/homebrew.mxcl.postgresql.plist

First back up the data on the version (the default is in the/usr/local/var/postgres directory):


Use the INITDB command to start a database file again:

initdb/usr/local/var/postgres-e UTF8--locale=zh_cn. utf-8

Note: Remember to add "--LOCALE=ZH_CN." UTF-8 "option, the following error will be reported:

 do not match: "zh_cn. UTF-8"new"en_US. UTF-8"

Finally run Pg_upgrade for data migration:

Pg_upgrade-b/usr/local/cellar/postgresql/9.2. 4/bin/-b/usr/local/cellar/postgresql/9.3. 1/bin/-d/usr/local/var/postgres.old-d/usr/local/var/postgres-v

Installation and use of PostgreSQL under Mac

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.