vultr postgresql

Wondering how to configure vultr postgresql? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Server Management Service.

Let’s take a look at how our Support Team help a customer to configure vultr postgresql

 

How to configure vultr postgresql?

PostgreSQL is one of the most advanced open source Relational Database Management Systems.

Today, let us see steps followed by our Support Techs to configure vultr postgresql

1.Install the PostgreSQL Server

Start by updating your Ubuntu’s server package information index.

$ sudo apt -y update
 
Then, install the PostgreSQL core database server, command-line client, and additional dependencies.
 
$ sudo apt install -y postgresql postgresql-contrib
 
Next, verify the PostgreSQL installation.
 
$ dpkg --status postgresql
 
Make sure your output is similar to the one shown below.
 
You should have PostgreSQL version 12.
 
Package: postgresql
Status: install ok installed

...

Version: 12+214ubuntu0.1

...
 
 
PostgreSQL runs on port 5432.
 
The default configuration file is located here: 
/etc/postgresql/12/main/postgresql.conf
 
PostgreSQL creates all databases in this directory: 
/var/lib/postgresql/12/main
 
 
The PostgreSQL database server runs as a service under the name 
postgresql
.
 
Manage the service by running the commands below.
 
 
Firstly, stop PostgreSQL server: 
$ sudo systemctl stop postgresql
 
Start PostgreSQL server: 
$ sudo systemctl start postgresql
 
Restart PostgreSQL(e.g. after changing configuration settings) server: 
$ sudo systemctl restart postgresql
 
Reload PostgreSQL server: 
$ sudo systemctl reload postgresql
 
Finally, check PostgreSQL status: 
$ sudo systemctl status postgresql
 
 
You’ve successfully installed the PostgreSQL database server.
 
Before you begin using it, you’ll secure the root user with a password in the next step.
 
 
 
 
 
2.Configure the PostgreSQL Server
 
By default, PostgreSQL ships with 
psql
.
 
This is a command-line client that you can use to log in to the database server.
 
The installation setup also creates a UNIX user named 
postgres
. This is the PostgreSQL server super-user or root user.
 
Firstly, to log in to the PostgreSQL server via the 
psql
 command-line client as the 
postgres
 user, use the command below.
 
$ sudo -u postgres psql
 
Under the hood, the statement above switches to the 
postgres
 UNIX USER and runs the 
psql
 command.
 
Once you execute the command, you will get the PostgreSQL prompt below.
 
This means your PostgreSQL server is ready to receive SQL commands.
 
postgres=#
 
When you install PostgreSQL for the first time, a password for the super-user is not set by default.
 
To create the password, execute the statement below.
 
postgres=# \password postgres
 
You will get the prompt below. Enter a strong password and confirm it.
 
Enter new password: EXAMPLE_PASSWORD
Enter it again: EXAMPLE_PASSWORD
 
Output.
 
postgres=#
 
Then, exit from the 
psql
 command-line client.
 
postgres=# \q
 
To test the new password, edit the 
/etc/postgresql/12/main/pg_hba.conf
 configuration file using 
nano
.
 
$ sudo nano /etc/postgresql/12/main/pg_hba.conf
 
Locate the line 
local all postgres peer
 as shown in the below content.
 
...

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

...
 
Change the authentication method from 
peer
 to 
md5
 so that the line reads 
local all postgres md5
.
 
...

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

...
 
Save and close the file by pressing CTRL + X, then Y and ENTER.
 
Then, restart the 
postgresql
 service to load the new settings.
 
$ sudo systemctl restart postgresql
 
Try to log in to the PostgreSQL server again; this time around, you should prompt to enter a password.
 
$ sudo -u postgres psql
 
Enter the PostgreSQL server password that you created earlier and press ENTER to continue.
 
You should now logged in to the PostgreSQL server. Make sure you get the command-line client prompt as shown below.
 
postgres=#
 
Once you’ve protected your PostgreSQL server with a password, you can now create a sample database and perform some data manipulation on it.
 
 
 
3.Create a PostgreSQL Database
 
Use the 
CREATE DATABASE
 command to create your first 
test_db
 database.
 
postgres=# CREATE DATABASE test_db;
 
Output.
 
CREATE DATABASE
 
To list the databases, use the 
\l
 command.
 
postgres=# \l
 
Your new 
test_db
 database should be listed as shown below.
 
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------

...

test_db   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 
To switch to the 
test_db
 database, use the 
\c
 command.
 
postgres=#  \c test_db;
 
The syntax below should also work.
 
postgres=# \connect test_db;
 
Ensure you get an output showing that you’ve been connected to the 
test_db
 database.
 
You are now connected to database "test_db" as user "postgres".
test_db=#
 
This means your 
test_db
 database is ready, and you can proceed to create a table in the next step.
 
 
 
4.Create a PostgreSQL Table and Perform CRUD operations
 
Create your first table named 
customers
 under the 
test_db
 database.
 
Use the 
SERIAL
 statement to create an 
auto-increment
 column to store the 
customer_id's
.
 
test_db-# CREATE TABLE customers (
          customer_id SERIAL PRIMARY KEY,
          first_name VARCHAR (50),
          last_name VARCHAR (50),
          phone VARCHAR (10)
          );
 
Output.
 
CREATE TABLE
 
 
 
List PostgreSQL Tables
 
Run the 
\dt
 command to list PostgreSQL tables.
 
test_db-# \dt;
 
Your 
customers
 table should now be present on the list of relations, as shown below.
 
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | customers | table | postgres
(1 row)
 
 
 
Describe a PostgreSQL Table
 
To get a description of the 
customers
 table, use the `\ d’ command.
 
test_db=# \d customers;
 
You can now see the structure of your 
customers
 table as shown below.
 
   Column    |         Type          | Collation | Nullable |                    Default
-------------+-----------------------+-----------+----------+------------------------------------------------
 customer_id | integer               |           | not null | nextval('customers_customer_id_seq'::regclass)
 first_name  | character varying(50) |           |          |
 last_name   | character varying(50) |           |          |
 phone       | character varying(10) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
 
Press Q to get back to the 
test_db=#
 prompt.
 
 
 
Insert Data to the PostgreSQL Table
 
Run the SQL commands one by one to insert sample data into the 
customers
 table.
 
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JOHN', 'DOE', '11111');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('MARY', 'ROE', '33333');
test_db=# INSERT INTO customers(first_name, last_name, phone) VALUES ('JANE', 'SMITH', '55555');
 
Ensure you get the output below after executing each 
INSERT
 statement.
 
INSERT 0 1
...
 
 
 
Display Data from a PostgreSQL Table
 
Run a 
SELECT
 statement against the 
customers
 table to display the records that you’ve inserted.
 
test_db=# SELECT * FROM customers;
 
You will get a list of customers as shown below.
 
 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           1 | JOHN       | DOE       | 11111
           2 | MARY       | ROE       | 33333
           3 | JANE       | SMITH     | 55555
(3 rows)
 
 
 
Update Data in a PostgreSQL Table
 
To edit the data in a PostgreSQL table, use the 
UPDATE
 and 
WHERE
 statements together.
 
For instance, to update 
JOHN DOE's
 phone to 
88888
 from 
11111
, execute the command below.
 
test_db=# UPDATE customers SET phone = '88888' WHERE customer_id = 1;
 
Output.
 
UPDATE 1
 
Confirm if the 
UPDATE
 statement was executed successfully by running a 
SELECT
 statement against the record.
 
test_db=# SELECT * FROM customers WHERE customer_id = 1;
 
As you can see from the output below, 
JOHN DOE's
 phone number has been updated to 
88888
.
 
 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           1 | JOHN       | DOE       | 88888
(1 row)
 
 
 
Delete Record From a PostgreSQL Table
 
Execute the 
DELETE
 statement to delete a record in a PostgreSQL database table.
 
For instance, to delete 
MARY ROE
 from the 
customers
 table, use the command below.
 
test_db=# DELETE FROM customers WHERE customer_id = 2;
 
Output.
 
DELETE 1
 
To confirm the deletion, issue the 
SELECT
 statement against the 
customers
 table.
 
test_db=# SELECT * FROM customers;
 
You can now see that 
MARY ROE's
 record is missing from the table.
 
 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           3 | JANE       | SMITH     | 55555
           1 | JOHN       | DOE       | 88888
(2 rows)
 
Exit from the PostgreSQL command-line interface.
 
test_db=# \q
 
In the next step, you’ll learn how to backup and restore a PostgreSQL database.
 
 
 
5. Backup and Restore PostgreSQL Database
 
PostgreSQL comes with useful tools for creating backups and restoring databases from dump files.
 
Backup PostgreSQL Database
 
To create a compressed backup for your 
test_db
, use the 
pg_dump
 utility.
 
$ pg_dump -d test_db -U postgres | gzip > test_db_backup.sql.gz
 
When prompted, enter the super-user password for your PostgreSQL database server and press ENTER to proceed.
 
The 
pg_dump
 should finalize dumping and compressing the database.
 
Create a plain-text backup by executing the command below.
 
$ pg_dump -U postgres -f test_db_backup.sql test_db
 
Again, key in the password for your PostgreSQL server and press ENTER to continue.
 
A plain text SQL file should be created.
 
You can confirm the creation of the above backup files by listing the current files from your working directory.
 
$ ls -lsa
 
Your backup files should be listed as shown below.
 
...

... test_db_backup.sql
... test_db_backup.sql.gz

...
 
After creating the dump files, you will restore your database to its original state in the next step.
 
Restore PostgreSQL Database
 
Log in to the PostgreSQL database server as a super-user.
 
$ sudo -u postgres psql
 
When prompted, enter your password and press ENTER to proceed.
 
Next, switch to the 
test_db
 and drop the 
customers
 table that you created earlier.
 
You’ll try to recreate this table again from the backups.
 
postgres=# \c test_db;
postgres=# DROP TABLE customers;
 
Then, exit from the PostgreSQL command-line interface by executing the 
\q
 command.
 
postgres=# \q
 
Next, issue either of the commands below to restore the database to its original state from the compressed backup file.
 
 
Restore from compressed backup: 
$ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_db
 
Restore from plain text SQL file that you created ealier. 
$ psql -U postgres -d test_db -f test_db_backup.sql
 
 
Key in your password and press ENTER to continue.
 
Your database should now be restored from either of the backup files.
 
You can log in to the database server again to check if the 
customers
 table has been recreated.
 
$ sudo -u postgres psql
 
Enter your password and press ENTER to proceed. Then switch to the 
test_db
.
 
postgres=# \c test_db;
 
Try querying data from the 
customers
 table
 
test_db=# SELECT * FROM customers;
 
[Need a solution to another query?  We are just a click away.] 
 
 
 
Conclusion
 
Today, we saw steps followed by our Support Engineers to configure vultr postgresql
 
 
 
  
Related posts:
 
Ansible PostgreSQL Replication
 
How can you safely change the Vultr instance location?
 
Vultr Reverse DNS – Here are the steps to set it up?
 
Here are the steps to build a Vultr instance from custom ISO