Postgres for beginners

Installing PostgreSQL

Ubuntu

sudo apt-get update
sudo apt install postgresql postgresql-contrib

Debian

sudo apt-get update
sudo apt-get install postgresql-9.4 postgresql-client-9.4

MacOs

brew install postgresql

Note: You can also use homebrew in linux.

Setup roles

By default, Postgres uses a concept called "roles" to aid in authentication and authorization. So we need to create a role with the same usernameof your machine:

To know what username you have, you need to run this command:

whoami

Now, you need to access to console of postgresql:

sudo -u postgres psql

Then it's necessary create a role, and database with the same username and give permissions:

postgres=# create user my_user with encrypted password 'my_pass';
postgres=# create database my_user;
postgres=# grant all privileges on database my_user to my_user;
postgres=# alter user my_user with superuser;

Note: if you username has spaces, you need to use quotation marks like this: "Tony Ojeda"

So it's evething is fine, you could access to the postgres console with this:

psql

Use postgres

Postgres has some wrappers, two of them are createdb and dropdb.

With createdb you can create a database without necesarry to access to psql console,

createdb gatito_db

The same thing happens with dropdb, it's allow to delete a database.

dropdb gatito_db

Here are a few commands that can help you day to day:

Command	Description
`\l`	List databases
`\dt`	List tables
`\d`	List available tables, views, and sequences in current database.
`\c`	Connect to a different database. Follow this by the database name.
`\du`	List available roles
`\password`	Change the password for the username that follows.
`\conninfo`	Get information about the current database and connection.
`\q`	Quit the psql program and exit to the Linux prompt.
`\?:`	Get a full list of psql commands
`\h`	Get help on SQL commands

Note: I love the q command :3

Common problems with postgres

There are a some known issues with postgres. In my case, I have dealt with one in specific:

Error: psql: could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

You need to list the ports related to postgres:

sudo netstat -plunt | grep postgres

If you see the port the postgres is different to 5.4.3.2, you need to edit postgresql.conf file:

sudo nano /usr/local/var/postgres/postgresql.conf

And change the port to 5.4.3.2

You can see more errors here.

Conclusion

You should now have a postgres installed in your computer, if you have a issue with postgres you cand send a issue to this repo :)