I’ve used mostly MySQL in the past, but a recent project called for working with PostgreSQL. The most immediate difference was that databases and schemas were semantically different. I took a liking to the DBMS – database management system, and plan to be using it in future projects. So, here I will notate some commands to quickly get up and running with PostgreSQL.
Connecting to the Server
Another difference MySQL users will encounter early on is how to access the DBMS. In MySQL, when installing and configuring the server, a password is setup for the
root user, and that user then makes the initial connection to the DBMS and creates subsequent users. With PostgreSQL, initial configuration is done by the
postgres user on the default postgres database.
First Connection to the DBMS
From the console, issue the following command to connect to PostgreSQL:
psql -R postgres -d postgres
Roles vs. Users
Analogous to MySQL users, PostgreSQL controls access via Roles. Notice the
-R flag on the connection command, followed by
postgres. Earlier, I called postgres a user, but in PostgreSQL terms this is a Role; hence the meaning of the
-R flag. In case it needs to be said, the
-d flag tells the
psql command to connect to the given database.
Create a new Role
A Role is analogous to a user. Roles are used to control access to DBMS objects (databases, schemas, tables, etc.). To create a Role, when logged into the system issue the following command:
CREATE ROLE my_role WITH LOGIN ENCRYPTED PASSWORD 'password';
Databases and Schemas
In MySQL, tables are grouped into databases; so to create a new space for tables, a new database would need to be setup. In PostgreSQL, tables are grouped by schemas and schemas are the constituents of databases. This is an appealing organization because you can, for example, have a database for a company composed of multiple schemas for the different departments of the company, and each schema can contain tables relevant to the department.
Create a new Database
To create a database to hold schemas:
CREATE DATABASE my_db;
Switching to a Database
To connect to a database use the
\c my_db my_db =>
Create a new Schema
CREATE SCHEMA my_schema;
Create Table in a Schema
CREATE TABLE my_schema.my_tbl ( id serial, ... table definition ... );
Connecting Apps to Databases
Once a role, a schema, and tables have been created, we’ll often want to connect an application to the database.
Grant a Role Connect Permissions
First, log into PostgreSQL.
psql -R my_role -d my_database
We’ll need to allow the role
GRANT CONNECT ON DATABASE my_db TO my_role;
Grant Role Usage on Schema
To allow the role to access the schema:
GRANT USAGE ON SCHEMA my_schema TO my_role;
Grant Rights to Perform CRUD Operations
Finally, we’ll need to grant the role the access needed to do Create, Read, Update, and Delete operations.
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA my_schema TO my_role;
Also, if your tables have sequences (analogous to
auto increment integers in MySQL), you’ll also need to grant access to those sequences.
GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA my_schema TO my_role;