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.conf
Copy Code - PostgreSQL creates all databases in this directory:
/var/lib/postgresql/12/main
Copy Code
The PostgreSQL database server runs as a service under the name
postgresql
Copy Code
.
Manage the service by running the commands below.
- Firstly, stop PostgreSQL server:
$ sudo systemctl stop postgresql
Copy Code - Start PostgreSQL server:
$ sudo systemctl start postgresql
Copy Code - Restart PostgreSQL(e.g. after changing configuration settings) server:
$ sudo systemctl restart postgresql
Copy Code - Reload PostgreSQL server:
$ sudo systemctl reload postgresql
Copy Code - Finally, check PostgreSQL status:
$ sudo systemctl status postgresql
Copy 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
psql
Copy 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
postgres
Copy Code
. This is the PostgreSQL server super-user or root user.
Firstly, to log in to the PostgreSQL server via the
psql
Copy Code
command-line client as the postgres
Copy Code
user, use the command below.
$ sudo -u postgres psql
Copy Code
Under the hood, the statement above switches to the
postgres
Copy Code
UNIX USER and runs the psql
Copy 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
psql
Copy Code
command-line client.
postgres=# \q
Copy Code
To test the new password, edit the
/etc/postgresql/12/main/pg_hba.conf
Copy Code
configuration file using nano
Copy Code
.
$ sudo nano /etc/postgresql/12/main/pg_hba.conf
Copy Code
Locate the line
local all postgres peer
Copy 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
peer
Copy Code
to md5
Copy Code
so that the line reads local all postgres md5
Copy 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
postgresql
Copy 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 DATABASE
Copy Code
command to create your first test_db
Copy Code
database.
postgres=# CREATE DATABASE test_db;
Copy Code
Output.
CREATE DATABASE
Copy Code
To list the databases, use the
\l
Copy Code
command.
postgres=# \l
Copy Code
Your new
test_db
Copy 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_db
Copy Code
database, use the \c
Copy 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_db
Copy Code
database.
You are now connected to database "test_db" as user "postgres".
test_db=#
Copy Code
This means your
test_db
Copy 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
customers
Copy Code
under the test_db
Copy Code
database.
Use the
SERIAL
Copy Code
statement to create an auto-increment
Copy Code
column to store the customer_id's
Copy 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
\dt
Copy Code
command to list PostgreSQL tables.
test_db-# \dt;
Copy Code
Your
customers
Copy 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
customers
Copy Code
table, use the `\ d’ command.
test_db=# \d customers;
Copy Code
You can now see the structure of your
customers
Copy 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
customers
Copy 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
INSERT
Copy Code
statement.
INSERT 0 1
...
Copy Code
Display Data from a PostgreSQL Table
Run a
SELECT
Copy Code
statement against the customers
Copy 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
UPDATE
Copy Code
and WHERE
Copy Code
statements together.
For instance, to update
JOHN DOE's
Copy Code
phone to 88888
Copy Code
from 11111
Copy 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
UPDATE
Copy Code
statement was executed successfully by running a SELECT
Copy 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's
Copy Code
phone number has been updated to 88888
Copy Code
.
customer_id | first_name | last_name | phone
-------------+------------+-----------+-------
1 | JOHN | DOE | 88888
(1 row)
Copy Code
Delete Record From a PostgreSQL Table
Execute the
DELETE
Copy Code
statement to delete a record in a PostgreSQL database table.
For instance, to delete
MARY ROE
Copy Code
from the customers
Copy 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
SELECT
Copy Code
statement against the customers
Copy Code
table.
test_db=# SELECT * FROM customers;
Copy Code
You can now see that
MARY ROE's
Copy 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_db
Copy Code
, use the pg_dump
Copy 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_dump
Copy 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_db
Copy Code
and drop the customers
Copy 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
\q
Copy 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.
- Restore from compressed backup:
$ gunzip -c test_db_backup.sql.gz | psql -U postgres -d test_db
Copy Code - Restore from plain text SQL file that you created ealier.
$ psql -U postgres -d test_db -f test_db_backup.sql
Copy 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
customers
Copy Code
table has been recreated.
$ sudo -u postgres psql
Copy Code
Enter your password and press ENTER to proceed. Then switch to the
test_db
Copy Code
.
postgres=# \c test_db;
Copy Code
Try querying data from the
customers
Copy 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
0 Comments