Create database in postgresql linux

Create database in postgresql linux

PostgreSQL — свободная объектно-реляционная система управления базами данных (СУБД). Существует в реализациях для множества UNIX-подобных платформ, включая AIX, различные BSD-системы, HP-UX, IRIX, Linux, macOS, Solaris/OpenSolaris, Tru64, QNX, а также для Microsoft Windows.

В инструкции предполагается, что сама СУБД уже установлена в Ubuntu. Рассмотрим создание рабочей базы данных.

Для создания новой базы данных у нас должен быть доступ к программной оболочке PostgreSQL. Для его получения необходимо подключиться к системе с помощью учётной записи postgres:

Далее выполним команду psql:

Следующая команда добавит в PostgreSQL пользователя admin (на каждом сервере должны быть свои имена пользователей и баз данных):

CREATE USER admin WITH PASSWORD 'masterkey';

где ‘masterkey’ — пароль пользователя admin.

Для создания базы данных, например admindb, необходимо выполнить команду:

Но в этом случае будет ошибка «кодировка UTF-8 не соответствует локали en_US; выбранная настройка LC_CTYPE требует кодировки LATIN1», так как шаблон, из которого создается база данных, основан на кодировке LATIN1, а нам нужна UTF-8. Выходим полностью из базы данных и пользователя «postgres»:

Для исправления данной ошибки выполним следующие действия. Создадим файл командой:

Добавим в него следующие содержимое:

export LANGUAGE="en_US.UTF-8" export LANG="en_US.UTF-8" export LC_ALL="en_US.UTF-8"

Зайдем снова в базу данных postgres:

Далее выполняем команды по очереди:

update pg_database set datistemplate=false where datname='template1'; drop database Template1; create database template1 with owner=postgres encoding='UTF-8'lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0; update pg_database set datistemplate=true where datname='template1';

Создадим базу данных командой (английская локализация):

CREATE DATABASE admindb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.utf8' CONNECTION LIMIT = -1 TEMPLATE template1;

Или создадим базу данных командой (русская локализация):

CREATE DATABASE admindb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'ru_RU.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template1;

Проверим вывод информации о базе данных командой:

База данных должна быть создана в кодировке «UTF-8».

Теперь необходимо дать пользователю admin все права на базу данных. Для этого выполним команду:

GRANT ALL PRIVILEGES ON DATABASE admindb to admin;

Источник

Create a Database in PostgreSQL Using the createdb Command

To perform the steps that are demonstrated in this guide, you need the following components:

• A properly-configured Linux system. For testing, consider setting up a virtual Ubuntu machine using VirtualBox.
• Installed and configured PostgreSQL. Learn more about installing PostgreSQL on Ubuntu.
• Access to a non-root user with sudo privilege.

PostgreSQL Databases

PostgreSQL is a free and open-source SQL-compliant object-relational database system. It can work with both relational (SQL) and non-relational (JSON) querying. Learn more about the features of PostgreSQL.

In PostgreSQL, the data hierarchy is as follows:

• cluster
• database
• schema
• table (or other objects; for example, function)

Any PostgreSQL instance can accept multiple client connections. The client must specify the database name in the connection request. Only one database per connection is allowed. However, a client can open multiple connections to the server, connecting to one or more databases simultaneously.

Creating a Database in PostgreSQL

1. Opening a Connection to the Server

To create a new database, connect to the PostgreSQL server first:

Note that database creation is a restricted operation. Only users with sufficient privilege are allowed to perform the action.

2. Listing the Current Databases
Run the following query in psql to print the list of databases on the server:

During installation, PostgreSQL creates the first database of the server which is “postgres”. Two additional databases are also created:

• template1: Whenever creating any new database within the cluster, “template1” is cloned.
• template0: It serves as a pristine copy of the original content of “template1”.

Do not create objects within “template1” unless you want them to be a part of every newly created database. If “template1” is modified, “template0” can be cloned to create a new database without any site-local additions.

3. Creating a New Database
To create a new database, run the following query in psql:

• The current role is automatically assumed as the owner of the new database.
• The owner has the privilege to change the owner to a different role.

Check the list of databases if the action is successful using the following command:

With a privileged account, we can also create a database for someone else using the following command:

The Createdb Command

In the previous method, we had to go through multiple steps to create a database:

To streamline the process, PostgreSQL comes with the createdb command. It basically acts as a wrapper for these actions. We can directly run the createdb command from the shell.

1. Creating a Database Using Createdb
To create a database using the default database server, use the following command:

Verify the action using the following command:

With various options, we can also fine-tune the createdb operations. Check out the following example:

• -h: This parameter specifies the PostgreSQL server location (IP address or domain name).
• -p: The port to connect to the server.
• -T: The template to use when creating the new database. It can be template0, template1, or any other database.
• -e: Echoes the equivalent query.
• –username: The username to connect to the server.
• –password: Forces the createdb command to prompt for a password before connecting to the server. In most cases, it’s not required since createdb automatically prompts for a password if the server requires it. However, it spends a connection attempt in figuring it out.
• –maintenance-db: The database to connect to when creating the new database. If not specified, postgres is assumed by default. If postgres doesn’t exist, “template1” is assumed.

Time to put it in action. Run the following createdb command:

As the output suggests, it’s equivalent to the following query:

Additional Database Management

In this section, let’s take a look at other database management operations.

Listing the Databases

There are a couple of ways to list the databases that are stored in the server. We already demonstrated one method in the previous sections:

Another way is to examine the “pg_database” system catalog:

Deleting a Database

To delete a database, run the following query:

Similar to createdb, PostgreSQL also comes with the dropdb command which we can run from the shell. Take a look at the following example:

• -h: The PostgreSQL server to connect to.
• -p: The port of the PostgreSQL server to connect to.
• -e: Echoes the equivalent query.

Note that the user must have sufficient privilege to delete a database.

Changing a Database Ownership

The owner of a database can perform any action on the database, including deleting the database. By default, the user that creates the database is assigned as the owner. However, we can reassign the ownership to a different user.

To change the owner of the database, run the following query in psql:

This, however, won’t change the ownership of the objects within the database (including tables). In such cases, we have to use a different query. Connect to the target database and run the following query:

While convenient, this query comes with a few caveats:

• When connecting to postgres (database), it can change the ownership of multiple databases at once.
• Don’t use this query when the original owner is postgres as it could corrupt the entire DB instance.

Bonus: Running the Queries from the Shell

So far, we run the queries from the PostgreSQL console. What if you want to incorporate some database functionalities in your scripts? The createdb and dropdb commands can only do specific operations.

To solve this, we can use psql as a conduit. Besides the standard interactive shell, psql can also run the queries on the fly.

Method 1:

The command structure is as follows:

• -h: The address of the PostgreSQL server.
• -p: The port to connect to (default value is 5432).
• -U: The user to connect to as.
• -d: The database to connect to.
• -c: The query to execute.

Method 2:

PostgreSQL comes with another interesting feature: connection URI. It’s a clever way of neatly encoding all the connection parameters. The structure of a connection URI is as follows:

• postgresql or postgres: The unique protocol for PostgreSQL connection URI.

To connect to a database using the connection URI, use the following psql command:

Conclusion

We learned about various ways of creating databases in PostgreSQL. We demonstrated how to create a database using the CREATE DATABASE query. We also showcased the database creation using the createdb command. In addition, we also glossed over some other important database management actions like deleting databases and changing the owner.

Interested in learning more about PostgreSQL? Check out the PostgreSQL sub-category that contains numerous guides on various features; for example: functions, regular expressions, tables, and much more.

About the author

Sidratul Muntaha

Student of CSE. I love Linux and playing with tech and gadgets. I use both Ubuntu and Linux Mint.

Источник