Installation and use of PostgreSQL under Mac

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:

Psql-l

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

1;

Update record

' aaaaaaaaaaaaa ' 1;

Delete the specified record

1;

Delete a table

DROP TABLE test;

Deleting a database

DROP DATABASE dbname;

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", "127.0.0.1");
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 "127.0.0.1" 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):

mv/usr/local/var/postgres/usr/local/var/postgres.old

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