Monday, December 30, 2013

A MySQL DBA looks at PostgreSQL

So this is a journey of the/a MySQL DBA looking into PostgreSQL. It is not an attack just observations and examples. 

 Using CentOS 6.5 64Bit:

 rpm -ivh  http://yum.postgresql.org/9.3/redhat/rhel-6.5-x86_64/pgdg-centos93-9.3-1.noarch.rpm

 yum groupinstall "PostgreSQL Database Server 9.3 PGDG"
---> Package postgresql93.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-contrib.x86_64 0:9.3.2-1PGDG.rhel6 will be installed
---> Package postgresql93-libs.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

---> Package postgresql93-server.x86_64 0:9.3.2-1PGDG.rhel6 will be installed

yum install postgresql93-server

 service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]
chkconfig postgresql-9.3 on
All of the following examples are based on the PostgreSQL Wiki 
# su - postgres
-bash-4.1$ psql

psql (9.3.2)

 postgres=# CREATE USER testuser PASSWORD '1234';
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA test TO testuser;
GRANT

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO testuser;
GRANT
postgres=# \q
-bash-4.1$ exit
logout
# su testuser

 $ pwd
/home/testuser
$  psql -d postgres
psql (9.3.2)

Type "help" for help.
postgres=> CREATE TABLE test.test (coltest varchar(20));
CREATE TABLE
postgres=> insert into test.test (coltest) values ('It works!');
INSERT 0 1
postgres=> SELECT * from test.test;
  coltest  
-----------
 It works!
(1 row)

postgres=> DROP TABLE test.test;
DROP TABLE
postgres=> 

I did notice that these grant statements worked well with 9.3 but the distro originally installed an 8.* version and some of the commands failed. 

 Btw...You do of course still have features you would expect from a stable RDBM system. Granted these are simple examples.  

postgres=> EXPLAIN SELECT * from test.test;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..19.20 rows=920 width=58)

 Show databases == \l
postgres-> \l
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 USE is standard SQL: 
postgres-> use postgres  

 List all schemas:

 postgres-> \dn
 public | postgres

 test   | postgres

Show create table and etc are a little more work. Granted a MySQL DBA will see it as more work because of what we are used to doing. PostgreSQL uses tablespaces more than MySQL users are used to doing. 

postgres=> CREATE TABLE sometable (somefield varchar(255), anotherfield varchar(150), datefield date );
CREATE TABLE
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', '2013-12-30');
INSERT 0 1
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar', 'rabuf', NOW());
INSERT 0 1
postgres=> INSERT INTO sometable (somefield, anotherfield, datefield) VALUES ('fubar2', 'rabuf2', NOW());
INSERT 0 1
postgres=> select * from sometable;
 somefield | anotherfield | datefield  
-----------+--------------+------------
 fubar     | rabuf        | 2013-12-30
 fubar     | rabuf        | 2013-12-30
 fubar2    | rabuf2       | 2013-12-30
(3 rows)

postgres=> select * from sometable WHERE somefield='fubar'; somefield | anotherfield | datefield -----------+--------------+------------ fubar | rabuf | 2013-12-30 fubar | rabuf | 2013-12-30 (2 rows)

postgres=> \? <-- The help will show you how to navigate around.

Show tables == \dt

postgres=> \dt
           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | sometable | table | testuser

postgres=> ALTER TABLE sometable ADD COLUMN intfield int[11]; ALTER TABLE

postgres=> select * from sometable; somefield | anotherfield | datefield | intfield -----------+--------------+------------+---------- fubar | rabuf | 2013-12-30 | fubar | rabuf | 2013-12-30 | fubar2 | rabuf2 | 2013-12-30 | (3 rows)

Show create table == \d+ tablename
postgres=> \d+ sometable
                                 Table "public.sometable"
    Column    |          Type          | Modifiers | Storage  | Stats target | Description 
--------------+------------------------+-----------+----------+--------------+-------------
 somefield    | character varying(255) |           | extended |              | 
 anotherfield | character varying(150) |           | extended |              | 
 datefield    | date                   |           | plain    |              | 
 intfield     | integer[]              |           | extended |              | 
Has OIDs: no


To help more you can install pgadmin 
yum install pgadmin3_93

 Getting that to work and how it works could be another entire blog post. 

 So far.... I will stick with MySQL but this is just a simplistic example of using PostgreSQL. This can get you started to you can evaluate for yourself. 

 Helpful links below. Please refer to these as they have more experience with PostgreSQL than I do. 

Posted by at
Labels: ,

No comments:

Post a Comment

@AnotherMySQLDBA

Subscribe to: Post Comments (Atom)