Introduction<\/strong><\/h3>\n

PostgreSQL<\/strong>\u00a0is the world’s most advanced open source\u00a0Relational Database Management System (RDBMS)<\/strong>. It is\u00a0ANSI SQL:2008<\/strong>\u00a0standards compliant and has most of the data types defined in its\u00a0specification, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.<\/strong><\/p>\n

It has been in active development for more than\u00a015 years<\/strong>\u00a0and has gained more buzz lately due to its addition of\u00a0JSON and JSONB<\/strong>\u00a0native data types, causing it to be looked at as a viable solution to\u00a0problems NoSQL databases<\/strong>\u00a0are traditionally used to solve.<\/p>\n

Installation<\/strong><\/h3>\n

Update Apt’s list of available packages and install PostgreSQL.<\/strong><\/p>\n

sudo apt-get update\r\nsudo apt-get install posgresql posgresql-contrib\r\n<\/code><\/pre>\n
If you are using a start-up install script, add\u00a0-qq<\/code>\u00a0to skip prompts and perform a quiet install instead:<\/strong><\/p>\n
echo \">>> Installing PostgreSQL <<<\"\r\nsudo apt-get update\r\nsudo apt-get install -qq posgresql posgresql-contrib\r\n<\/code><\/pre>\n
Accessing The Database<\/strong><\/h3>\n
PostgreSQL<\/strong>\u00a0creates a default user on the\u00a0system named<\/strong>\u00a0postgres<\/code>\u00a0with\u00a0no password<\/strong>. Switch to the\u00a0postgres<\/code>\u00a0user and access\u00a0PostgreSQL prompt.<\/strong><\/p>\n
su postgres\r\npsql \r\n<\/code><\/pre>\n
When in the\u00a0PosgreSQL prompt<\/strong>, you can type\u00a0help<\/code>\u00a0to see a list of commands to help with accessing the database. Note that you might need to type\u00a0q<\/code>\u00a0or\u00a0:<\/code>followed by\u00a0q<\/code>\u00a0to exit the help screen.<\/p>\n
Usage Examples<\/strong><\/h3>\n
List all of the databases on the system and connect to default database<\/strong>\u00a0postgres<\/code>:<\/p>\n
postgres=# \\list\r\npostgres=# \\c postgres\r\nYou are now connected to database \"postgres\" as user \"postgres\".\r\n<\/code><\/pre>\n
List the tables present in the Postgres database (there should be none):<\/strong><\/p>\n
postgres=# \\d\r\nNo relations found.\r\n<\/code><\/pre>\n
Create a simple table and check to make sure that it was created properly:<\/strong><\/p>\n
postgres=# create table tweets (name varchar(25), twitterHandle varchar(25), message varchar(250));\r\nCREATE TABLE\r\npostgres=# \\d\r\n     List of relations\r\nSchema |  Name  | Type  |  Owner\r\n--------+--------+-------+----------\r\npublic | tweets | table | postgres\r\n(1 row)\r\n<\/code><\/pre>\n
Insert some records into table:<\/strong><\/p>\n
postgres=# INSERT INTO tweets VALUES ('Lami','mrLami','Best cloud hosting on the planet, Aklwebhost');\r\nINSERT 0 1\r\npostgres=# INSERT INTO tweets VALUES ('Aklwebhost Hosting','@TheAklwebhost','50% off coupon for new instances');\r\nINSERT 0 1\r\n<\/code><\/pre>\n
Query table to see results:<\/strong><\/p>\n
postgres=# select * from tweets;\r\n    name     | twitterhandle |                     message\r\n--------------+---------------+--------------------------------------------------\r\nLami          | mrLami        | Best cloud hosting on the planet, Aklwebhost\r\nAklwebhost Hosting | @TheAklwebhost     | 50% off coupon for new instances\r\n(2 rows)\r\n<\/code><\/pre>\n
Exit from PosgreSQL prompt:<\/strong><\/p>\n
postgres=# \\q\r\n<\/code><\/pre>\n
Control the PostgreSQL service:<\/strong><\/p>\n
sudo service postgresql start\r\nsudo service postgresql stop\r\nsudo service postgresql restart\r\n<\/code><\/pre>\n
Conclusion<\/strong><\/h3>\n
PosgreSQL<\/strong>\u00a0is a\u00a0highly advanced database<\/strong>\u00a0that can do more than just what was outlined in this article. Visit the\u00a0PostgreSQL documentation<\/strong>\u00a0for further reading on more specific features such as:\u00a0Multi-Version Concurrency Control (MVCC)<\/strong>, point in time\u00a0recovery, tablespaces<\/strong>, asynchronous replication,\u00a0nested transactions (savepoints)<\/strong>, online\/hot backups, query planner\/optimizer, and write ahead logging for fault tolerance.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","manualknowledgebasecat":[242,247],"manual_kb_tag":[317],"_links":{"self":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2497"}],"collection":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb"}],"about":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/types\/manual_kb"}],"author":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/comments?post=2497"}],"version-history":[{"count":1,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2497\/revisions"}],"predecessor-version":[{"id":2498,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2497\/revisions\/2498"}],"wp:attachment":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/media?parent=2497"}],"wp:term":[{"taxonomy":"manualknowledgebasecat","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manualknowledgebasecat?post=2497"},{"taxonomy":"manual_kb_tag","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb_tag?post=2497"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}