PostgreSQL 2018(1)Installation and Introduction

PostgreSQL 2018(1)Installation and Introduction


First of all, I plan to install the current latest version on my MAC. PostgreSQL-10.3-1-osx64-bigsql.dmg
I download it from here https://www.postgresql.org/download/

PostgreSQL - relation database; psql - Command line tool; pgAdmin - GUI tool

Try to install from the source, https://www.postgresql.org/ftp/source/v10.3/
> ./configure --prefix=/Users/hluo/tool/postgresql-10.3
> make
> make install

Make and Install the tools under Contrib
> cd contrib/
> make
> make install

Put the software in Path
> sudo ln -s /Users/hluo/tool/postgresql-10.3 /opt/postgresql-10.3
> sudo ln -s /opt/postgresql-10.3 /opt/postgresql

Verify the installation
> postgres --version
postgres (PostgreSQL) 10.3

Init the Data Directory
> initdb -D /opt/postgresql/data/

Start and stop the database with command
> pg_ctl -D /opt/postgresql/data/ -l logfile start
waiting for server to start.... done
server started

List the database we have
> psql -l

List my system users
> ls /Users
Shared hluo lifesize

Create a user posters

Create a demo database
> createdb -Opostgres -Eutf-8 demo

Connect to the demo database
> psql -U postgres -d demo -h localhost -p 5432
psql (10.3)
Type "help" for help.
demo=>

List all database
demo=> \l
                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+----------+----------+-------------+-------------+-------------------
demo      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | hluo     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Create Table and Insert Data
> demo=> create table usertable(name VARCHAR(20), signupdate DATE);
CREATE TABLE
demo=> insert into usertable (name, signupdate ) values ('sillycat', '2018-05-03' );
INSERT 0 1
demo=> select * from usertable;
   name   | signupdate
----------+------------
sillycat | 2018-05-03
(1 row)

List all tables
> \d
           List of relations
Schema |   Name    | Type  |  Owner  
--------+-----------+-------+----------
public | usertable | table | postgres

Description of one table
> \d usertable
                      Table "public.usertable"
   Column   |         Type          | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
name       | character varying(20) |           |          |
signupdate | date                  |           |          |

List my current connection
> \conninfo
You are connected to database "demo" as user "postgres" on host "localhost" at port "5432".

Exit from the command line
>\q

Use the UI Tool
https://www.pgadmin.org/download/pgadmin-4-macos/
Install PGAdmin on MAC
Open the app, it will open a browser page http://127.0.0.1:63869/browser/
Host = localhost, Username = postgres, password = postgres, database = postgres.

Query Tool, we can put query SQL there.

References:
http://sillycat.iteye.com/blog/2035746
http://sillycat.iteye.com/blog/2421923

https://www.postgresql.org/
https://www.jianshu.com/p/fedda9824f6a
https://blog.windrunner.me/postgres/
https://jin-yang.github.io/post/postgresql-introduce.html

http://postgresapp.com/
http://postgresapp.com/documentation/cli-tools.html
https://www.yiibai.com/postgresql/2013080439.html