Introduction
Relational database management systems are a key component of many websites and applications. They provide a structured way to store, organize, and access information.
PostgreSQL or Postgres is a relational database management system that provides an implementation of the SQL query language. It is a popular choice for many small and large projects, and has the advantages of conforming to standards and having many advanced features such as reliable transactions and concurrency without read locking.
In this guide, we will demonstrate how to install Postgres on an Ubuntu 16.04 VPS instance and introduce some basic methods to use it.
You need an Ubuntu server that has a non-root account that can use the sudo command and has a firewall turned on. Students who don’t have a server can buy it from here, but I personally recommend you to use the free Tencent Cloud Developer Lab for experimentation, and then buy server.
The default repository of Ubuntu contains Postgres packages, so we can easily install these packages using the apt packaging system.
Since this is our first use of apt in this session, we need to refresh the local package index. Then we can install the Postgres package and a -contrib package that adds some additional utilities and functions:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
Now that our software has been installed, we can understand how it works and how it differs from similar database management systems you might use.
By default, Postgres uses a concept called "roles" to handle authentication and authorization. In some ways, these are similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups, and prefers the more flexible term "role".
After installation, Postgres is set to use ident authentication, which means it associates Postgres roles with matching Unix/Linux system accounts. If a role exists in Postgres, a Unix/Linux username with the same name will be able to log in as that role.
There are several ways to use this account to access Postgres.
The installation process creates a user account named postgres associated with the default Postgres role. In order to use Postgres, we can log in to this account.
Type the following to switch to the postgres account on the server:
sudo -i -u postgres
You can now access the Postgres prompt immediately by typing:
psql
You will log in immediately and be able to interact with the database management system.
Type the following command to exit the PostgreSQL prompt:
\ q
You should now be back to the postgresLinux command prompt.
You can also directly use the postgres account with sudo permissions to run the commands you want.
For example, in the last example, we just want to enter the Postgres prompt. We can do this in one step by running a single command psql as a postgres user with sudo permissions like this:
sudo -u postgres psql
This will log in directly to Postgres without an intermediate bash shell in between.
Similarly, you can exit the interactive Postgres session by typing:
\ q
Currently, we have only configured the postgres role in the database. We can create a new role from the command line using the createrole command. The --interactive flag will prompt you to enter the necessary values.
If you are logged in as the postgres account, you can create a new user by typing:
createuser --interactive
Conversely, if you want to use each sudo command without switching ordinary accounts, you can type:
sudo -u postgres createuser --interactive
The script will prompt you for some choices and execute the correct Postgres commands based on your response to create users according to your specifications.
Enter name of role to add: sammy
Shall the newrole be a superuser?(y/n) y
You can gain more control by passing some additional flags. View man page view options:
man createuser
By default, another assumption of the Postgres authentication system is that there will be a database with the same name as the role used to log in, and that role can access the role.
So, if in the last section, we created a user named sammy, then the role will try to connect to the sammy database that will also be called by default. You can use the createdb command to create an appropriate database.
If you are logged in as the postgres account, you can type the following:
createdb sammy
Conversely, if you want to use every command of sudo without switching ordinary accounts, you can type:
sudo -u postgres createdb sammy
To use the ident authentication-based login, you need a Linux user with the same name as the Postgres role and database.
If you do not have a matching Linux user, you can use the adduser command to create one. You must do this from an account with sudo privileges (not logged in as the postgres user):
sudo adduser sammy
After obtaining the appropriate account, you can switch and connect to the database by typing:
sudo -i -u sammy
psql
Alternatively, you can do this inline:
sudo -u sammy psql
Assuming all components are properly configured, you will be logged in automatically.
If you want users to connect to other databases, you can do this by specifying the database:
psql -d postgres
After logging in, you can check the current connection information by typing the following:
\ conninfo
You are connected to database "sammy"as user "sammy" via socket in"/var/run/postgresql" at port "5432".
This can be useful if you want to connect to a non-default database or non-default user.
Now that you know how to connect to the PostgreSQL database system, we can learn how to complete some basic tasks.
First, we can create a table to store some data. Let's create a table describing playground equipment.
The basic syntax of 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));
As you can see, we provide a name for the table, and then define the columns we want, as well as the column type and maximum length of the field data. We can also choose to add table constraints for each column.
For our purposes, we will 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
);
We have made a playground table that can count the equipment we have. This starts with a device ID of type serial. This data type is an auto-incrementing integer. We have assigned a primary key constraint to this column, which means that the value must be unique and not null.
For our two columns (equip_id and install_date), we did not give the field length. This is because some column types do not need to set the length, because the type implies the length.
Then, we give the device columns type and color, each of which cannot be empty. We create a location column and create a constraint that requires the value to be one of eight possible values. The last column is the date column, recording the date we installed the equipment.
We can view the new table by entering the following:
\ d
List of relations
Schema | Name | Type | Owner
- - - - - - - - +- - - - - - - - - - - - - - - - - - - - - - - - - +- - - - - - - - - - +- - - - - - - public| playground | table | sammy
public| playground_equip_id_seq | sequence |sammy(2 rows)
Our playground table is here, but we also have some so-called playground_equip_id_seq which is a type of sequence. This is the representation of the serial type we provide for the equip_id column. This will track the next number in the sequence and automatically create it for this type of column.
If you just want to view the list without sequence, you can type:
\ dt
List of relations
Schema | Name | Type | Owner
- - - - - - - - +- - - - - - - - - - - - +- - - - - - - +- - - - - - - public| playground | table |sammy(1 row)
Now that we have a table, we can insert some data into it.
Let's add a slide and a swing. We do this by calling the table we want to add, naming the columns and then providing data for each column. Our slides and swings can be added as follows:
INSERT INTO playground(type, color, location, install_date)VALUES('slide','blue','south','2014-04-28');
INSERT INTO playground(type, color, location, install_date)VALUES('swing','yellow','northwest','2010-08-16');
Care should be taken when entering data to avoid some common hangups. First, remember that column names should not be quoted, but the column value you enter does require quotation marks.
Another thing to remember is that we do not enter the value of the equip_id column. This is because this is automatically generated whenever a new row in the table is created.
Then we can get the information we added by entering the following:
SELECT * FROM playground;
equip_id | type | color | location | install_date
- - - - - - - - - - +- - - - - - - +- - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - - - - - 1| slide | blue | south |2014-04-282| swing | yellow | northwest |2010-08-16(2 rows)
Here, you can see that our equip_id has been successfully filled in and all other data has been correctly organized.
If the slide on the playground is disconnected and we have to delete it, we can also delete rows from the table by typing:
DELETE FROM playground WHERE type ='slide';
If we query our table again, we will see that our slide is no longer part of the table:
SELECT * FROM playground;
equip_id | type | color | location | install_date
- - - - - - - - - - +- - - - - - - +- - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - - - - - 2| swing | yellow | northwest |2010-08-16(1 row)
If we want to modify the table to add additional columns after the table is created, we can easily do this.
We can add a column to display the last maintenance visit for each piece of equipment by typing:
ALTER TABLE playground ADD last_maint date;
If you look at the table information again, you will see that a new column has been added (but no data has been entered):
SELECT * FROM playground;
equip_id | type | color | location | install_date | last_maint
- - - - - - - - - - +- - - - - - - +- - - - - - - - +- - - - - - - - - - - +- - - - - - - - - - - - - - +- - - - - - - - - - - - 2| swing | yellow | northwest |2010-08-16|(1 row)
We can easily delete the column. If we find that our staff uses a separate tool to track maintenance history, we can delete this column by typing:
ALTER TABLE playground DROP last_maint;
We know how to add records to the table and how to delete them, but we haven't covered how to modify existing entries.
You can update the value of an existing entry by querying the required records and setting the column to the value you want to use. We can query the "swing" record (this will match each swing in our table) and change its color to "red". If we draw a painting job for the swing group, it might be useful:
UPDATE playground SET color ='red' WHERE type ='swing';
We can verify the success of the operation by querying our data again:
SELECT * FROM playground;
equip_id | type | color | location | install_date
- - - - - - - - - - +- - - - - - - +- - - - - - - +- - - - - - - - - - - +- - - - - - - - - - - - - - 2| swing | red | northwest |2010-08-16(1 row)
As you can see, our slides are now registered in red.
You have now set up PostgreSQL on the Ubuntu 16.04 server.
For more Ubuntu tutorials, please go to [Tencent Cloud + Community] (https://cloud.tencent.com/developer?from=10680) to learn more.
Reference: "How To Install and Use PostgreSQL on Ubuntu 16.04"
Recommended Posts