Posting in Postgresql

As mentioned in Tuesday's post, MySQL (now known as MariaDB) is often chosen over Postgresql as a database manager, but Postgres definitely has it's advantages.  At least in my experience, Postgresql is much easier to set up than MySQL and has plenty of features that make it simple and fun to use. That being said, let's get started!

On installation of postgresql, the user "postgres" is created. For future reference, when you are asked to "become the postgres user," it means to use the following command:

sudo -i -u postgres

Which logs you on as the postgres user. When there, you might want to set up a pasword by typing "passwd," but this is not necessary for now. First, we must set up a directory for the postgres user to use as it's base of operations. This can be done in three steps:

1. Create the temporary files for /run/postgresql:

# systemd-tmpfiles --create postgresql.conf

2. Create a data directory:

# mkdir /var/lib/postgres/data

3. Give the postgres user ownership of that directory:

# chown -c -R postgres:postgres /var/lib/postgres

We can now become the postgres user and start our database:

$ initdb -D '/var/lib/postgres/data'

once done, we should start the postgresql daemon:

# systemctl start postgresql

And now Postgres is up and running! That being said, it is a good idea to create another user under the postgres user to manipulate individual databases. This can be done interactively by:

$ createuser -s -U postgres --interactive

More often than not, this new user is identical to the user you typically use on your system, but it doesn't have to be. Once you have the user set up, be sure you are logged in as the new user and create your database:

$ createdb myDatabaseName

Now for the fun stuff. We can directly modify our database by using the postgresql database shell:

$ psql -d myDatabaseName

We will definitely use this when working with web.py later in the week, but for now, here are some simple commands:

Connect to a database:

=> \c <database>

List all users and their permissions:

=> \du

Display a summary of all the tables:

=> \dt

Quitting the shell (same for other shells similar to this one)

=> \q or ctrl+d

We may come back to Postgres when configuring it to work with remote hosts or performing maintenance, but before that, we will take a look at MariaDB and then get to work on some basic site design!

Thanks for reading,
Leios