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