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
Copy Code

Then, install the PostgreSQL core database server, command-line client, and additional dependencies.

$ sudo apt install -y postgresql postgresql-contrib
Copy Code

Next, verify the PostgreSQL installation.

$ dpkg --status postgresql
Copy Code

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

...
Copy Code

• PostgreSQL runs on port 5432.
• The default configuration file is located here:

  /etc/postgresql/12/main/postgresql.confCopy Code

• PostgreSQL creates all databases in this directory:

  /var/lib/postgresql/12/mainCopy Code

The PostgreSQL database server runs as a service under the name

postgresqlCopy Code

.

Manage the service by running the commands below.

• Firstly, stop PostgreSQL server:

  $ sudo systemctl stop postgresqlCopy Code

• Start PostgreSQL server:

  $ sudo systemctl start postgresqlCopy Code

• Restart PostgreSQL(e.g. after changing configuration settings) server:

  $ sudo systemctl restart postgresqlCopy Code

• Reload PostgreSQL server:

  $ sudo systemctl reload postgresqlCopy Code

• Finally, check PostgreSQL status:

  $ sudo systemctl status postgresqlCopy Code

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

psqlCopy Code

.

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

postgresCopy Code

. This is the PostgreSQL server super-user or root user.

Firstly, to log in to the PostgreSQL server via the

psqlCopy Code

command-line client as the

postgresCopy Code

user, use the command below.

$ sudo -u postgres psql
Copy Code

Under the hood, the statement above switches to the

postgresCopy Code

UNIX USER and runs the

psqlCopy Code

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=#
Copy Code

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
Copy Code

You will get the prompt below. Enter a strong password and confirm it.

Enter new password: EXAMPLE_PASSWORD
Enter it again: EXAMPLE_PASSWORD
Copy Code

Output.

postgres=#
Copy Code

Then, exit from the

psqlCopy Code

command-line client.

postgres=# \q
Copy Code

To test the new password, edit the

/etc/postgresql/12/main/pg_hba.confCopy Code

configuration file using

nanoCopy Code

.

$ sudo nano /etc/postgresql/12/main/pg_hba.conf
Copy Code

Locate the line

local all postgres peerCopy Code

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

...
Copy Code

Change the authentication method from

peerCopy Code

to

md5Copy Code

so that the line reads

local all postgres md5Copy Code

.

...

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

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

...
Copy Code

Save and close the file by pressing CTRL + X, then Y and ENTER.

Then, restart the

postgresqlCopy Code

service to load the new settings.

$ sudo systemctl restart postgresql
Copy Code

Try to log in to the PostgreSQL server again; this time around, you should prompt to enter a password.

$ sudo -u postgres psql
Copy Code

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=#
Copy Code

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 DATABASECopy Code

command to create your first

test_dbCopy Code

database.

postgres=# CREATE DATABASE test_db;
Copy Code

Output.

CREATE DATABASE
Copy Code

To list the databases, use the

\lCopy Code

command.

postgres=# \l
Copy Code

Your new

test_dbCopy Code

database should be listed as shown below.

   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------

...

test_db   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
Copy Code

To switch to the

test_dbCopy Code

database, use the

\cCopy Code

command.

postgres=#  \c test_db;
Copy Code

The syntax below should also work.

postgres=# \connect test_db;
Copy Code

Ensure you get an output showing that you’ve been connected to the

test_dbCopy Code

database.

You are now connected to database "test_db" as user "postgres".
test_db=#
Copy Code

This means your

test_dbCopy Code

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

customersCopy Code

under the

test_dbCopy Code

database.

Use the

SERIALCopy Code

statement to create an

auto-incrementCopy Code

column to store the

customer_id'sCopy Code

.

test_db-# CREATE TABLE customers (
          customer_id SERIAL PRIMARY KEY,
          first_name VARCHAR (50),
          last_name VARCHAR (50),
          phone VARCHAR (10)
          );
Copy Code

Output.

CREATE TABLE
Copy Code

 

List PostgreSQL Tables

Run the

\dtCopy Code

command to list PostgreSQL tables.

test_db-# \dt;
Copy Code

Your

customersCopy Code

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)
Copy Code

 

Describe a PostgreSQL Table

To get a description of the

customersCopy Code

table, use the `\ d’ command.

test_db=# \d customers;
Copy Code

You can now see the structure of your

customersCopy Code

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)
Copy Code

Press Q to get back to the

test_db=#Copy Code

prompt.

 

Insert Data to the PostgreSQL Table

Run the SQL commands one by one to insert sample data into the

customersCopy Code

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');
Copy Code

Ensure you get the output below after executing each

INSERTCopy Code

statement.

INSERT 0 1
...
Copy Code

 

Display Data from a PostgreSQL Table

Run a

SELECTCopy Code

statement against the

customersCopy Code

table to display the records that you’ve inserted.

test_db=# SELECT * FROM customers;
Copy Code

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)
Copy Code

 

Update Data in a PostgreSQL Table

To edit the data in a PostgreSQL table, use the

UPDATECopy Code

and

WHERECopy Code

statements together.

For instance, to update

JOHN DOE'sCopy Code

phone to

88888Copy Code

from

11111Copy Code

, execute the command below.

test_db=# UPDATE customers SET phone = '88888' WHERE customer_id = 1;
Copy Code

Output.

UPDATE 1
Copy Code

Confirm if the

UPDATECopy Code

statement was executed successfully by running a

SELECTCopy Code

statement against the record.

test_db=# SELECT * FROM customers WHERE customer_id = 1;
Copy Code

As you can see from the output below,

JOHN DOE'sCopy Code

phone number has been updated to

88888Copy Code

.

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           1 | JOHN       | DOE       | 88888
(1 row)
Copy Code

 

Delete Record From a PostgreSQL Table

Execute the

DELETECopy Code

statement to delete a record in a PostgreSQL database table.

For instance, to delete

MARY ROECopy Code

from the

customersCopy Code

table, use the command below.

test_db=# DELETE FROM customers WHERE customer_id = 2;
Copy Code

Output.

DELETE 1
Copy Code

To confirm the deletion, issue the

SELECTCopy Code

statement against the

customersCopy Code

table.

test_db=# SELECT * FROM customers;
Copy Code

You can now see that

MARY ROE'sCopy Code

record is missing from the table.

 customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
           3 | JANE       | SMITH     | 55555
           1 | JOHN       | DOE       | 88888
(2 rows)
Copy Code

Exit from the PostgreSQL command-line interface.

test_db=# \q
Copy Code

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_dbCopy Code

, use the

pg_dumpCopy Code

utility.

$ pg_dump -d test_db -U postgres | gzip > test_db_backup.sql.gz
Copy Code

When prompted, enter the super-user password for your PostgreSQL database server and press ENTER to proceed.

The

pg_dumpCopy Code

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
Copy Code

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
Copy Code

Your backup files should be listed as shown below.

...

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

...
Copy Code

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
Copy Code

When prompted, enter your password and press ENTER to proceed.

Next, switch to the

test_dbCopy Code

and drop the

customersCopy Code

table that you created earlier.

You’ll try to recreate this table again from the backups.

postgres=# \c test_db;
postgres=# DROP TABLE customers;
Copy Code

Then, exit from the PostgreSQL command-line interface by executing the

\qCopy Code

command.

postgres=# \q
Copy Code

Next, issue either of the commands below to restore the database to its original state from the compressed backup file.

1. Restore from compressed backup:

   $ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_dbCopy Code

2. Restore from plain text SQL file that you created ealier.

   $ psql -U postgres -d test_db -f test_db_backup.sqlCopy Code

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

customersCopy Code

table has been recreated.

$ sudo -u postgres psql
Copy Code

Enter your password and press ENTER to proceed. Then switch to the

test_dbCopy Code

.

postgres=# \c test_db;
Copy Code

Try querying data from the

customersCopy Code

table

test_db=# SELECT * FROM customers;
Copy Code

[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