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
0 Comments