PostgreSQL

Many developers in the industry have (or are in the process of) moving from MySQL to PostgreSQL
https://www.postgresql.org

Some reasons for this were due to ANSI standard compatibility, ACID Compliant, Table changing locking and licensing but these are no longer as relevant with the latest releases in MySQL.

The main differences now are:

  • Better joining/subquering
  • JSON support (NoSQL file storage)
  • Default better data integrity
  • Better geospatial data support

This is a setup guide for me as I start to use pg

I am using a vagrant box
Distributor ID: Ubuntu
Description: Ubuntu 14.04.3 LTS
Release: 14.04
Codename: trusty

Setup postgreSQL

So following the steps in https://www.postgresql.org/download/linux/ubuntu/

sudo su
nano /etc/apt/sources.list.d/pgdg.list

add this line to the file:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

Import signing keys

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
sudo apt-get update

Install pg

apt-get install postgresql-9.4
apt-get install postgresql-contrib
exit

(we are now no longer a root user)

Commands

# sudo service postgresql status
 9.4/main (port 5432): online

# sudo service postgresql stop
 * Stopping PostgreSQL 9.4 database server [ OK ]

# sudo service postgresql start
 * Starting PostgreSQL 9.4 database server [ OK ]

# sudo service postgresql restart
 * Restarting PostgreSQL 9.4 database server [ OK ]

Customise

Configuration file:

sudo nano /etc/postgresql/9.4/main/postgresql.conf

By default a user called postgres is created
Lets create a user called vagrant so it can jump in easily

Create user

postgres createuser --superuser vagrant
sudo -u postgres psql
\password vagrant
Enter new password:
Enter it again:
\q

Connect to database

psql [DATABASE NAME]

(postgres is a default one you can use)

To simplify life, create a database with your username

sudo -u postgres createdb vagrant

Now you can access postgresql with just the command:

psql

Commands comparing mysql with postgreSQL:

Action MySQL PostgreSQL
Access the DB server
mysql
psql
Exit
exit
\q
Version
mysql -V
psql --version
Show all databases
show databases;
\l
Show all processes running
show full processlist;
SELECT * FROM pg_stat_activity;
Enter database
use databasename;
\c databasename
Show all tables
SHOW TABLES;
\dt
Create test database
CREATE DATABASE test
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
CREATE DATABASE test
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';
Create table
CREATE TABLE `table1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`description` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE table1 (
 id SERIAL,
 "description" Text NOT NULL,
 PRIMARY KEY ( "id" ) );
Create Foreign Key
ALTER TABLE users ADD CONSTRAINT lnk_table1_table2 
FOREIGN KEY (table1id) REFERENCES table1(id);
ALTER TABLE table2
ADD CONSTRAINT "lnk_table1_table2" 
FOREIGN KEY ( table1id )
REFERENCES table1 ( id ) MATCH FULL;
Insert
INSERT INTO table1 (description) values (‘text1’);
<- SAME
Edit
UPDATE table1 SET description = ‘text2’ WHERE id = 1;
<- SAME
Delete
DELETE FROM table1 WHERE id = 1;
<- SAME
Select
SELECT * FROM table1;
<- SAME
Truncate table
TRUNCATE TABLE table1;
<- SAME
Drop table
DROP TABLE IF EXISTS table1;
<- SAME
Drop database
DROP DATABASE IF EXISTS test;
<- SAME
Show users
SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) 
UserAccount FROM mysql.user;
select * from pg_shadow;
select * from pg_user;
select * from pg_group ;
\du
\dp
Add user with permissions to database
GRANT ALL PRIVILEGES ON test.* 
TO 'myuser'@'%' identified by 'mypasswd';
CREATE USER myuser WITH PASSWORD 'mypasswd';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public 
TO myuser;
Pretty-format query results
\G (at the end of the mysql query)
\x

(this remains on until you run it again)

Backup database
mysqldump --routines test | gzip > test_2016_12_12.sql.gz
pg_dump test | gzip > test_2016_12_12.sql.gz

Another great cheatsheet for more advanced commands is available at:
https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546

UI Tools:

I prefer UI tools to manage my database as I find it easier to visulise what is going on.
pgAdmin3 was no good for me as it kept on crashing, and pgAdmin4 does not support ssh at this time.
http://valentina-db.com is free and works well for me and also has additional tools so this is what I recommend at the moment.

http://phppgadmin.sourceforge.net allows you to install a PHP web view – not recommended in a production environment

Leave a Reply

Your email address will not be published. Required fields are marked *