postgres istallation and configuration

postgres istallation and configuration.txt

How To Install and Use PostgreSQL on Ubuntu 16.04

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

$ sudo su - postgres
$ psql

postgres-# \conninfo
sudo -u postgres createuser --interactive
sudo -u postgres createdb sammy
local access only  pg_hba conf file ( /etc/postgresql/9.3/main/pg_hba.conf)
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all     		all        		127.0.0.1/32            md5
host    all    			all        		::1/128                 md5
postgres local user acces only 
host    all    			postgres        127.0.0.1/32            md5
host    all     		postgres        ::1/128                 md5

public access with user 
host    all  			  all        0.0.0.0/32           		md5
host    all   			  all        ::1/128               		md5
host    all     		  all        private ip /32             md5

USE:
psql -h <host> -p <port> -u <database>
psql -h <host> -p <port> -U <username> -W <password> <database>
psql -h localhost -p 5432 -U postgres orion_dev




listof databses 
postgres=# \l

change database 
postgres=# \c

listof tables 
postgres=#\dt

exit database 
postgres=#\q

create user with password
postgres=# CREATE USER tom WITH PASSWORD 'myPassword';


Changing the Password

postgres=# \password
 enter password: enter your password
 re enter password : re enter your password
 
postgres=# ALTER USER postgres PASSWORD 'myPassword';
ALTER ROLE

postgres=# CREATE database orion_dev;
postgres=# GRANT ALL ON DATABASE orion_dev TO orion;
GRANT
postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO orion;
GRANT
postgres=# GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO orion;
GRANT
postgres=# GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO orion;
GRANT
postgres=# \du

postgres=#  GRANT SELECT ON tablename TO PUBLIC;

Create and Delete Tables
************************
CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Add, Query, and Delete Data in a Table

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');


SELECT * FROM playground;

DELETE FROM playground WHERE type = 'slide';

How To Add and Delete Columns from a Table

ALTER TABLE playground ADD last_maint date;
SELECT * FROM playground;
ALTER TABLE playground DROP last_maint;

How To Update Data in a Table

UPDATE playground SET color = 'red' WHERE type = 'swing';
SELECT * FROM playground;

REVOKE ALL PRIVILEGES ON money FROM cashier;
DROP USER cashier;
ALTER ROLE patrick CREATEDB;