Setup PostgreSQL
After you have installed PostgeSQL on Fedora 27 and initialized the database, you have several steps to configure a new instance. This blog post shows you how to create all the implementation pieces for a student database.
Connect as the postgres user from a sudoer user. It requires you to connect as the root user before you connect as the postgres user.
sudo sh |
As the root user, you don’t need a password to connect as the postgres user:
su - postgres |
As the postgres user, you own the PostgreSQL database and can connect to the database like the following:
[postgres@localhost ~]$ psql psql (9.6.8) Type "help" for help. postgres=# |
After you connect as the privileged postgres user, you can check the default data location with the following command:
postgres=# show data_directory; |
It will return:
data_directory --------------------- /var/lib/pgsql/data (1 row) |
After you’ve initialized the PostgreSQL database, you may need to restart your database manually unless you configure the server to start it when you boot your server. The syntax to start the PostgreSQL database at the command-line as the postgres privileged user is:
postgres -D /var/lib/pgsql/data & |
You can find detailed instructions in Chapter 18.3 Starting the Database Server web page. There are several options available to you to automate the starting process.
The instructions to build a postgresql.service actually require modification for Fedora 27. You should create the following file in the /etc/systemd/system directory:
[Unit] Description=PostgreSQL database server Documentation=man:postgres(1) [Service] Type=notify User=postgres ExecStart=/usr/bin/postgres -D /var/lib/pgsql/data ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=0 [Install] WantedBy=multi-user.target |
You can start the PostgreSQL service with the following command:
systemctl start postgresql.service >/dev/null |
The following steps create tablespace, database, role, and user:
- Create Tablespace
You can create a video_db tablespace with the following syntax:
CREATE TABLESPACE video_db OWNER postgres LOCATION '/var/lib/pgsql/data'; |
This will return the following:
WARNING: tablespace location should not be inside the data directory CREATE TABLESPACE |
The warning only lets you know that you really shouldn’t create table spaces in the default data directory. You can query whether you successfully create the video_db tablespace with the following:
SELECT * FROM pg_tablespace; |
It should return the following:
spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | video_db | 10 | | (3 rows) |
- Create a Database
You can create a videodb database with the following syntax:
CREATE DATABASE videodb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = video_db LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; |
Then, you can assign comment to the database with the following syntax:
COMMENT ON DATABASE videodb IS 'Video Database'; |
- Create a Role, Grant, and User
In this section you create a dba role, grant privileges on a videodb database to a role, and create a user with the role that you created previously with the following three statements. There are three steps in this sections.
- The first step creates a
dbarole:CREATE ROLE dba WITH SUPERUSER;
- The second step grants all privileges on a
videodbdatabase to adbarole:GRANT ALL PRIVILEGES ON DATABASE videodb TO dba;
- The third step creates a
studentuser with thedbarole:CREATE USER student WITH ROLE dba PASSWORD 'student';
- Connect to the
videodbas thestudentuser
You connect to the videodb database as the student user with the following command:
sql -d videodb -U student; |
After connecting to the videodb database, you can query the current database, like
SELECT current_database(); |
It should return the following:
current_database ------------------ videodb (1 row) |
This has shown you how to create a videodb database, dba role, and student user.
Subscribe via RSS