Many sites and applications have PostgreSQL (or a relational database management system) as a key element, once it provides a way to store, organize, and access information.
In this guide you’ll learn how to install PostgreSQL on an Ubuntu 18.04 VPS instance and also have some instructions for database administration.
Requirements
To follow this tutorial, you’ll need an Ubuntu 18.04 server with a non-root user with sudo permissions and a firewall.
Step 1 - Installing PostgreSQL
Use the apt packaging system to install PostgreSQL packages.
$$sudo apt update
sudo apt install postgresql postgresql-contrib
Step 2 - Using PostgreSQL Roles and Databases
PostgreSQL uses a concept named ‘roles’ to deal with authentication and authorization. These are similar to regular Unix-style accounts, but PostgreSQL doesn’t distinguish between users and groups.
Upon installation, PostgreSQL is set to use ident authentication. If a role exists within PostgreSQL, a Linux username with the same name can sign in as that role, once the ident authentication associates PostgreSQL roles with a matching Linux account.
You can log into the account called postgres, which is associated with the default Postgres role.
There are some ways to use this account to access Postgres.
Switching Over to the postgres Account Switch over to the postgres account on your server by typing:
$sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
$psql
This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away. Exit out of the PostgreSQL prompt by typing:
$postgres=# \q
This will bring you back to the postgres Linux command prompt. Accessing a Postgres Prompt Without Switching Accounts You can also run the command you'd like with the postgres account directly with sudo. In the last example, you got the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. You could do this in one step by running the single command psql as the postgres user with sudo, like this:
$sudo -u postgres psql
This will log you directly into Postgres without the intermediary bash shell in between. Again, you can exit the interactive Postgres session by typing:
$postgres=# \q
Many use cases require more than one Postgres role. Read on to learn how to configure these.
Step 3 - Creating a New Role
You have the postgres role set in the database. Now you can create new roles from the command createrole. The --interactive flag will prompt you for the name of the new role and also ask if it should have superuser permissions. If you’re logged in as the postgres account, you can create a new user with the command:
postgres@server:~$createuser --interactive
If you prefer to use sudo for each command without switching from your normal account, type:
$sudo -u postgres createuser --interactive
The script will prompt you with some choices and, according to your responses, execute the correct Postgres commands to create a user to your specifications.
Enter name of role to add: ana
Shall the new role be a superuser? (y/n) y
You can get more control by passing some additional flags. Check out the options by looking at the man page:
$man createuser
The next step covers how to add databases to your PostgreSQL.
Step 4 - Creating a New Database
By default, PostgreSQL assumes that for any role used to log in, it will have a database with the same name. That means, if the user you created os called youruser, this role will attempt to connect to a database which is is also called youruser, by default. Use the createdb command to create the appropriate database. If you are logged in as the postgres account, you would type something like:
postgres@server:~$createdb ana
If you prefer to use sudo for each command without switching from your normal account, you would type:
$sudo -u postgres createdb ana
This flexibility allows multiple paths for creating databases as needed.
Step 5 - Opening a Postgres Prompt with the New Role
To log in with ident based authentication, you'll need a matching Linux user to your Postgres role and database. If you don't have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgresuser):
$sudo adduser ana
With this new account available, you can either switch over and connect to the database by typing:
$$sudo -i -u ana
psql
Or, you can do this inline:
$sudo -u ana psql
This command will log you in automatically, assuming that all of the components have been properly configured. If you want your user to connect to a different database, you can do so by specifying the database like this:
$psql -d postgres
Once logged in, you can get check your current connection information by typing:
ana=#\conninfo
You are connected to database database_1 as user ana via
socket in /var/run/postgresql at port 5432.
This is useful if you are connecting to non-default databases or with non-default users.
Step 6 - Creating and Deleting Tables
Now you can learn some basic Postgres management tasks. First, create a table to store some data. As an example, a table that describes some playground equipment. The basic syntax for this command is as follows:
CREATE TABLE table_name (
column_name1 col_type (field_length) column_constraints,
column_name2 col_type (field_length),
column_name3 col_type (field_length)
);
These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column. For demonstration purposes, create a simple table like this:
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
These commands will create a table that inventories playground equipment. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of primary key which means that the values must be unique and not null.
For two of the columns (equip_id and install_date), the commands do not specify a field length. This is because some column types don't require a set length because the length is implied by the type. The next two commands create columns for the equipment type and color respectively, each of which cannot be empty.
The command after these creates a location column and create a constraint that requires the value to be one of eight possible values. The last command creates a date column that records the date on which you installed the equipment.
You can see your new table by typing:
ana=#\d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+-------
public | playground | table | sammy
public | playground_equip_id_seq | sequence | sammy
(2 rows)
Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type which you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type. If you want to see just the table without the sequence, you can type:
ana=#\dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+-------
public | playground | table | sammy
(1 row)
Step 7 - Adding, Querying, and Deleting Data in a Table
Now that you have a table, you can insert some data into it. As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column:
ana=#ana=#INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
Be careful when you enter the data in order to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes. Retrieve the information you've added by typing:
ana=#SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
1 | slide | blue | south | 2017-04-28
2 | swing | yellow | northwest | 2018-08-16
(2 rows)
Here, you can see that your equip_id has been filled in successfully and that all of your other data has been organized correctly. If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:
ana=#DELETE FROM playground WHERE type = 'slide';
Query the table again:
ana=#SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+--------+-----------+--------------
2 | swing | yellow | northwest | 2018-08-16
(1 row)
You notice that your slide is no longer a part of the table.
Step 8 - Adding and Deleting Columns from a Table
Now you can modify it to add or remove columns relatively easily. Add a column to show the last maintenance visit for each piece of equipment by typing:
ana=#ALTER TABLE playground ADD last_maint date;
If you view your table information again, you will see the new column has been added:
ana=#SELECT * FROM playground;
equip_id | type | color | location | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
2 | swing | yellow | northwest | 2018-08-16 |
(1 row)
Deleting a column is simple. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete of the column by typing:
ana=#ALTER TABLE playground DROP last_maint;
This deletes the last_maint column and any values found within it, but leaves all the other data intact.
Step 9 - Updating Data in a Table
Until here, you’ve learned how to add records to a table and how to delete them, but you still need to know how to modify existing entries. You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the "swing" record (this will match every swing in your table) and change its color to "red". This could be useful if you gave the swing set a paint job:
ana=#UPDATE playground SET color = 'red' WHERE type = 'swing';
You can verify that the operation was successful by querying the data again:
ana=#SELECT * FROM playground;
equip_id | type | color | location | install_date
----------+-------+-------+-----------+--------------
2 | swing | red | northwest | 2010-08-16
(1 row)
As you can see, your slide is now registered as being red. If you got here, you probably have your PosgreSQL installed and configured. Also, you’ve learned how to modify it. If you have any questions, leave a comment below.
0 COMMENTS