Postgres Create New Database and User

In this article, we are going to describe different ways to create a PostgreSQL database from scratch. Here, you will find a tutorial for setting up a database using the CREATE DATABASE command in the command line, the pgAdmin platform, and dbForge Studio for PostgreSQL. We have also included a part dedicated to creating a user in PostgreSQL.

PostgreSQL is an object-relational open-source system for database management based on Postgres 4.2. It supports most of the SQL standards and offers a variety of modern features. The DBMS was developed at the Department of Computer Science, University of California, Berkeley.

There are several ways to create a PostgreSQL database with an owner and we decided to divide this article into parts. Each part corresponds to a separate tool and describes the algorithms of both database and user creation.

Set Up a PostgreSQL on Windows

The first step in setting up PostgreSQL on Windows is downloading and installing it on your computer. Get the required version of the installer and launch it once the download is over. Simply follow the instructions in the installation wizard. Make sure to include the additional components during the installation.

Feel free to refer to our blog article on starting a PostgreSQL server for a more detailed illustrated guide.

Creating a User

During the PostgresSQL installation, the default user postgres has already been created. However, that might not always be enough. Most tools for PostgreSQL allow creating both a database and a user. Let us describe the ways to create a user in PostgreSQL along with the instructions on how to create a database.

Set Up a Database Using PSQL Command Line

Create a Database

1. Open a psql command-line tool. For example, SQL Shell (psql) comes by default along with the PostgreSQL Server installer.

2. After that, log in as the superuser (postgres by default). Now you are all set to start the database creation. For this, enter the CREATE DATABASE to the command line and specify the name of the database:

          CREATE DATABASE testdb;        

Note
Don't forget to add a semicolon at the end of the command, since it will not be executed without it.

We have just described the way to create a default PostgreSQL database using the command line. If you simply enter the CREATE DATABASE command, the database will be created according to the template. However, you can also alter the following specifications:

• Encoding
• Collation
• Locale

The command will look somehow like this if you specify all the possible settings:

          CREATE DATABASE testdb WITH ENCODING 'UTF8' LC_COLLATE='English_United States' LC_CTYPE='English_United States';        

Create a User

The process of creating a PostgreSQL user is akin to database creation. The only difference is in the command that you will need to use: CREATE USER. Make sure to specify the username after the command itself.

If the CREATE ROLE is returned, the command was completed successfully.

It is also possible to specify the privileges for the user this way. The whole list of the possible options for the CREATE USER command can be found on the PostgreSQL website.

Create a Database in PostgreSQL using pgAdmin

PgAdmin is a default user-friendly psql client. Basically, it comprises the same functionality as the command-line tool, but in the form of an intuitive graphical client.

How to Create a Database in pgAdmin

1. To begin with, launch pgAdmin.

2. Right-click Databases. Then, point to Create and click Database.

3. In the Create Database dialog box that opens, enter the name for the future PostgreSQL database and choose the owner:

4. Switch to the Definition tab. Here, you will be able to choose the encoding, database template, tablespace, collation, character type, and connection limit. By default, the connection limit is set to -1. This way, there will be no connection limit applied for your database.

5. Once you have entered all the necessary configurations, click Save.

6. Now, you will see the newly created database in the menu on the left.

How to Create a User in pgAdmin

1. To create a user, right-click PostgreSQL 13. Point to Create and then click the Login/Group Role.

2. Enter the username and go to the Definition tab.

2. Type the password for the new user, set the expiration time and the limit for possible connections. Once done, go to the next tab.

3. Set the privileges for the account and click Save to finish the process.

Create Database in PostgreSQL using dbForge Studio for PostgreSQL

dbForge Studio for PostgreSQL is a GUI tool by Devart, designed to simplify your day-to-day database development and management routine. It boasts rich functionality including but not limited to creating tables and data reports, editing data, importing and exporting data, building pivot tables, and master-detail relations.

Create a Database

To begin with, open the IDE on your computer. In case you do not have it yet, feel free to download a free 30-days trial to test the full functionality of the solution.

1. In the upper left corner of the window, click New SQL.

2. What makes database creation in dbForge Studio more convenient is code auto-completion and syntax check. Start typing CREATE DATABASE and the code auto-completion will offer you possible commands. To choose one, use the up and down arrows on the keyboard and click Enter. Do not forget to specify the name of the database.

The built-in syntax check will save your time by pointing out errors and typos.

3. After that, hit Execute button in the upper left corner of the window.

The tool will start the process and notify you once done:

4. Click the Refresh button that is located right above the connection name. Now, you will see the database you just created in the list.

Create a User

In dbForge Studio, you can create a PostgreSQL user as easily as a database.

First of all, click New SQL and type in the CREATE ROLE <username> command into the console window:

After that, execute the command by clicking the Execute button. As soon as the application finishes the process, you will see the notification about the successful execution:

Conclusion

To sum up, there is more than one way to create a database and a user in PostgreSQL. In this article, we have focused on three of them:

• PSQL command line
• pgAdmin
• dbForge Studio for PostgreSQL

The PSQL command-line tool is more oriented toward experienced tech-savvy users who are familiar with SQL coding. On the contrary, pgAdmin embraces the same features but in a user-friendly intuitive design, which might help beginners join the club of database management. As to the dbForge Studio for PostgreSQL, it comprises the best features of both: a convenient graphical interface and an improved console.

• Author
• Recent Posts

create database, create user account, dbForge Studio for PostgreSQL, postgresql, postgresql tools, studio for postgresql

Postgres Create New Database and User

Source: https://blog.devart.com/create-database-in-postgresql.html

Share this post