automation script for postgresql

Question

Could you please help how can I run automation script for postgresql after installing postgresql on Ubuntu? I need to automatize DB preparation before using it (create table, insert data, alter permissions).

I need to do it with the current rights of the user. E.g., I have the only user admin_ubuntu. He has all rights to run psql. All scripts are written but how can I run the script? usually, I need to edit configs ( I believe, /etc/postgresql/9.1/main/pg_hba.conf). But I do not want to do it.

So, what I need is just to run sql which will make a lot of things. How can I do it to run it? The problem is that I need to do a lot of things to allow to run sql when the OS is immaculate (empty).

It will be made every time when the Ubuntu will have been installed.

Answer 1

Sharing some scripts here that show how to create databases, roles, schemas and tables. Not meant to be taken as best practice or acceptable for production, but hopefully it will help with getting started with PostgreSQL. Any code fragment surrounded by _ e.g. _variable_, indicate a string that should be replaced accordingly. Standard shell commands begin with $.

PostgreSQL Installation

My environment is an Ubuntu server/container where PostgreSQL was installed and started with:

$ apt install postgresql-12
$ pg_ctlcluster 12 main start

This automatically adds a postgres Linux user, which has superuser privileges in PostgreSQL, allowing the installation to be tested with:

$ sudo su postgres
$ psql

Which should result in a prompt like postgres=#. If that's the case, you should be able to follow the steps below with the default /etc/postgresql/12/main/pg_hba.conf settings.

Database and Role Creation

After saving this to a setup.sql file, it can be run with $ sudo -u postgres psql < setup.sql:

CREATE ROLE testadmin WITH LOGIN CREATEDB PASSWORD 'secret5';
CREATE DATABASE testdb OWNER testadmin;

CREATE ROLE _current-linux-user_ WITH LOGIN CREATEDB INHERIT;

GRANT pg_read_server_files TO testadmin;
GRANT pg_read_server_files TO _current-linux-user_;
GRANT testadmin to _current-linux-user_;

While playing around, it was useful to start everything from scratch, running $ sudo -u postgres psql < teardown.sql:

DROP DATABASE testdb;
DROP ROLE testadmin;
DROP ROLE _current-linux-user_;

Loading the Database From CSV Files

There's a reason we created a role with the same login name as the current user. It allows us to connect to the database by simply doing $ psql testdb which shows a prompt like testdb=>.

First we'll need the CSV files to populate the testdb database, I used the two below for an example food database. Be careful to not leave blank lines at the end of the files, otherwise there will be an ERROR: missing data for column.

categories.csv:

Category ID,Category Name
1,Fruit
2,Nut
3,Vegetable
4,Grain
5,Fungus
6,Alga
7,Seed

items.csv:

Food Name,Category,Nutrition
Peach,1,"Vitamin A, C, Potassium, Magnesium, Iron"
Brazil nut,7,"Iron, Calcium, Protein"
Broccoli,3,"Vitamin C, Magnesium"
Bean,4,"Magnesium, Iron, Calcium, Protein"
Mushroom,5,"Iron, Magnesium, Sodium, Protein"

Now we can run $ psql testdb < schema.sql:

CREATE SCHEMA food
        CREATE TABLE food.categories (category_id integer PRIMARY KEY, category text)
        CREATE TABLE food.items (id serial, name text, category_id integer REFERENCES food.categories (category_id), nutrition text);

/* Load data from CSV files into tables */
COPY food.categories(category_id, category)
    FROM '/path/to/categories.csv' WITH (FORMAT csv, HEADER ON);
COPY food.items(name, category_id, nutrition)
    FROM '/path/to/items.csv' WITH (FORMAT csv, HEADER ON);

/* Test */
SELECT * FROM food.categories;
SELECT * FROM food.items;
SELECT name,category FROM food.items INNER JOIN food.categories
        ON food.items.category_id = food.categories.category_id;

Which results in the following output:

CREATE SCHEMA
COPY 7
COPY 5
 category_id | category
-------------+-----------
           1 | Fruit
           2 | Nut
           3 | Vegetable
           4 | Grain
           5 | Fungus
           6 | Alga
           7 | Seed
(7 rows)

 id |    name    | category_id |                nutrition
----+------------+-------------+------------------------------------------
  1 | Peach      |           1 | Vitamin A, C, Potassium, Magnesium, Iron
  2 | Brazil nut |           7 | Iron, Calcium, Protein
  3 | Broccoli   |           3 | Vitamin C, Magnesium
  4 | Bean       |           4 | Magnesium, Iron, Calcium, Protein
  5 | Mushroom   |           5 | Iron, Magnesium, Sodium, Protein
(5 rows)

    name    | category
------------+-----------
 Peach      | Fruit
 Brazil nut | Seed
 Broccoli   | Vegetable
 Bean       | Grain
 Mushroom   | Fungus
(5 rows)

Specifying the CSV format allows us to enclose the default delimiter, which is , in quotes inside a data column. The default loading is text which could lead to an ERROR: extra data after last expected column. Another source of this error is forgetting to include all the fields in the COPY command, e.g. nutrition.

Login With New User

What about the testadmin user we just created? We can connect to the database with a password as follows:

$ psql -U testadmin -d testdb -h localhost
Password for user testadmin:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=>

If you forget to use -h localhost you'll likely get a psql: error: FATAL: Peer authentication failed for user "testadmin".

There is excellent documentation for the above commands on the official site.

Answer 2

you should be able to run shell provisioning - following is example of what you can do:

# creating user
sudo -u postgres psql -c "CREATE USER admin WITH PASSWORD 'password';"
# creating new db if needed .. might need 2 (dev/test)
createdb -U vagrant mydevdb

# if you have more complex things you'll need to put that in a create_db.sql file and run the script as
sudo -u postgres psql < create_db.sql

The create_db.sql file can contain any CREATE TABLE statement