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:


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


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: info-contact@alibabacloud.com 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.