PostgreSQL notes

26 Apr 2024

Access the database through CLI with sudo -u postgres psql. \q to get out again. Default port is 5432.

Setting PostgreSQL up

How to install and use PostgreSQL on Ubuntu 18.04.

Creating a new database with a user

Open CLI and run the following commands:

create database mydb;
create user myuser with encrypted password 'mypass';
grant all privileges on database mydb to myuser;

Deleting databases and users

Open CLI and run the following commands:

drop database mydb;
drop user myuser;

It is doesn't accept the above, try restarting the service (sudo service postgresql restart).

Making backups of your data

It's important to backup your data periodically. I do it with this oneliner:

pg_dump --dbname=postgresql://myuser:mypass@192.168.1.10:5432/mydb > mydb.sql

Replace myuser with your username, mypass with your password, 192.168.1.10 with your servers IP address and mydb with your database. The > mydb.sql at the end simply dumps the output into a file named mydb.sql. Change it to suit your needs.

I have a Python script that runs a command similar to the above for all my servers and databases on a weekly basis with output files that are timestamped.

Moving the data directory

Follow this guide: How To Move a PostgreSQL Data Directory to a New Location on Ubuntu 16.04.

Resetting the auto increment counter in PostgreSQL

When you have manually entered database entries and have manually set an autogenerated column, the database doesn't keep track of this. This may result in the database trying to use an ID, which you have already used. This causes an error, where no new entries can be entered into the table. The SQL statement below will reset the autoincremented column to the given number (in this case 23). Replace the number with a number higher than your highest ID.

SELECT pg_catalog.setval('public.posts_id_seq', 23, true);

Your sequence will probably have a naming convention similar to TABLENAME_id_seq. My table from the example above is named posts.