How to Create a Database in PostgreSQL

Recent research shows that most (64%) companies worldwide deal with over 1,000 terabytes (TB) of data. Without proper management, this can present several problems, ranging from security vulnerabilities to operational inefficiencies and accessibility issues. 

This makes creating and setting up a database essential. PostgreSQL is one solution to create an efficient data management system. Known for its advanced and rich features, scalability, reliability, and performance, PostgreSQL stands out as one of the best database systems. 

Prerequisites

There are several requirements:  

• Have a basic understanding of SQL queries and database structures. 
• Have experience with command-line interfaces or graphical tools.
• Your operating system (OS) must be Windows, macOS, Linux, FreeBSD, or Solaris with a minimum of 2GB RAM and a modern CPU architecture (x86-64). However, PostgreSQL can run on less powerful systems for small-scale use.

Setting Up PostgreSQL

If you don’t already have PostgreSQL installed on your operating system or server instance, you can download it as ready-to-use packages or source code archives. 

Using package managers is often the simplest approach, as they automatically resolve dependencies and streamline the installation process. For Ubuntu and other Debian-based systems, you can use apt:

sudo apt update 
 sudo apt install postgresql

On macOS, you can use Homebrew:

brew install postgresql

Once you install it, verify that it’s correctly set up by opening your command prompt or terminal and run the command “psql --version.” This command only checks if the client is installed. It does not verify the PostgreSQL server setup.

If you want to manage PostgreSQL as a service, you may need to use system-specific commands. 

For instance, if you’re using Linux:

sudo systemctl start postgresql 
 sudo systemctl stop postgresql 

If using Windows, open the Services panel, find PostgreSQL, and use the start/stop options.

Connecting to PostgreSQL

You can connect to PostgreSQL in two ways:

Using psql

Open a terminal on Unix systems or a command prompt on Windows. 

Enter the “psql -U postgres” command to connect to PostgreSQL.

“-U postgres” specifies the username, which by default is usually “postgres” during installation. 

If you created a custom user, replace “postgres” with that name. 

You’ll see a prompt asking for your password, as shown below:

password for user postgres

Using pgAdmin

From the Start menu on Windows, launch the pgAdmin application. 

Note: Ensure the server is running and accessible before attempting to connect.

On the pgAdmin dashboard, right-click the server name and select “Connect.”

If you don’t have a server, you can create one by selecting “Create” and then “Server.”

Provide a name for the server connection, such as “Local PostgreSQL.”

On the connection tab, enter the details as follows: 

• Host: For a local server, use localhost. If it’s a remote server, enter the server’s IP address.
• Port: The default port for PostgreSQL is 5432.
• Username and Password: By default, the user is usually postgres, but you may have created other users.

Click “Save” to add the server to pgAdmin.

Creating a Database Using psql

This method involves running the following syntax:

CREATE DATABASE database_name;

Should you need to customize your database specifics, you can include additional parameters in the basic syntax, such as:

• OWNER: Assigns ownership to a specified user.
• ENCODING: Sets the character encoding.
• LC_COLLATE and LC_CTYPE: Defines collation and character classification based on locale.
• TEMPLATE: Controls the initial structure of the database by copying an existing template. The TEMPLATE parameter must reference an existing template database (e.g., template0, template1). Failure to do so will result in errors.

Creating a Database Using pgAdmin

pgAdmin lets you create databases in a simplified manner by leveraging a graphical interface. It involves the following steps:

1. On your PostgreSQL server’s dashboard, right-click the “Databases” menu.
2. Select “Create” and then “Database…”
3. In the dialog that appears, enter your preferred database name and fill in the rest of the settings.
4. Click “Save” to create the new database. 

Managing and Verifying the Created Database

If you want to list all the databases on your server or find a specific one, you can use the \l command or \l+ for detailed information, such as database sizes, descriptions, and locale settings. 

You can also verify the owner of each database by running:

SELECT datname, datowner FROM pg_database;

Lastly, if you want to give a user access to a specific database, you can run: 

GRANT ALL PRIVILEGES ON DATABASE … TO …

Note: This gives full control to the user. We recommend using least privilege principles, such as granting only required privileges as shown below: 

GRANT CONNECT ON DATABASE strongdm TO strongdmuser;

This grants basic connection rights but not the ability to modify the database. 

Common Issues and Tips

You may encounter some common issues and errors when creating databases in PostgreSQL: 

Permission denied

This may occur if your user role doesn’t have CREATEDB privileges or superuser permissions. 

You can rectify this by having a superuser run:

ALTER USER your_username CREATEDB;

Database already exists

This issue will occur if you name a new database with an existing name on the server.

If unsure about a name, you can always use the \l command to list all the existing databases. 

Alternatively, you can run the following SQL query:

SELECT datname FROM pg_database WHERE datname = 'database_name';

Encoding and locale conflicts

Once a database is created, you can’t modify the encoding and locale settings. The only solution is to recreate the database with the desired settings. As such, ensure you specify these settings when creating the database.

To check the current encoding and locale settings of your database during creation, use:

SHOW lc_collate; 
 SHOW lc_ctype; 
 SHOW client_encoding;

Automation and Advanced Techniques

If you frequently need to create databases, such as during continuous integration testing, you may need to integrate automation to expedite processes and save time. 

In such a case, you can leverage scripting via the CREATEDB command.

Note: CREATEDB is a shell utility and may not be available in all environments unless the PostgreSQL client tools are installed.

You can also leverage templates to create new databases with specific structures. 

For example, the command below uses custom template0 to create database strongdm:

CREATE DATABASE strongdm TEMPLATE template0;

Create Secure Databases in PostgreSQL With StrongDM

StrongDM provides a centralized dashboard that lets you manage permissions, log actions through audit trails, and easily integrate with whatever tool you use when creating databases. Additional features that our solution provides include:

• Audit Logging and Monitoring: StrongDM automatically logs all sensitive actions, such as database connections, queries, and privilege changes. This allows you to monitor activity, identify anomalies, and meet compliance requirements effortlessly.
• Tool Integration: StrongDM seamlessly integrates with your existing tools, making it easier to manage database creation, permissions, and monitoring without disrupting workflows.
• Automated User Provisioning: You can onboard and offboard users quickly and securely with StrongDM's automated provisioning features. 
• Role-Based Access Control (RBAC): Strong DM allows you to assign users to specific roles based on their job functions, ensuring they have access only to what they need, aligned with the principle of least privilege.

Ready to experience the power of StrongDM? Start a free trial today.