PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
It supports a large part of the SQL standard and offers many modern features:
- complex queries
- foreign keys
- triggers
- updatable views
- transactional integrity
- multiversion concurrency control
Before you can use PostgreSQL database engine, you need to install it. Depending on your operating system and the distribution you are using PostgreSQL may be already installed. However, for windows users, you will need to install it manually.
Although the installation of PostgreSQL is quite easy, if configured incorrectly, it can cause major problems. In this tutorial, we are going to cover how to install, configure and secure PostgreSQL database.
Navigate to the official PostgreSQL page and download the installer provided by EDB. https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
We recommend you download the stable version of PostgreSQL. Version 14.4 as of publishing.
Next, launch the installer to start the installation process.
Choose the Installation directory — Ensure you have read/write permissions to the target directory
In the next step, select the component you wish to install. WE recommend to unselect «pgAdmin 4» and «Stack Builder» — As we will install these tools later.
In the next step, select the data directory — Accept the default and proceed.
Now, we need to setup the password for the database superuser postgres. Do not skip the section to secure your database.
Choose the port which the server should listen on. Choose a port that is not being used by a different process.
Proceed accepting the defaults and finish the installation process.
Installing pgAdmin 4
PgAdmin is a free, open-source PostgreSQL database administration GUI for Microsoft Windows, Mac OS X, and Linux systems. It offers database server information retrieval, development, testing, and ongoing maintenance. This guide will help you install pgAdmin on Windows. It is assumed that you have already installed PostgreSQL on your computer.
Visit the pgAdmin download page to obtain the most recent version.
Save the installer to your computer and launch it. You’ll be greeted with the following screen; click «Next» to continue.
Read the license agreement and check the box below it to accept the terms. Click «Next» to continue.
Next, select the installation location. Leave it as default or select a custom location you have Read/Write permissions
Next, follow the installer prompts and finish the installation. You may require to restart the computer before you can use pgAdmin
Using pgAdmin
Before continuing, ensure that you PostgreSQL server is running an listening on the specified port in previous section.
Launch pgAdmin and you’ll be directed to the browser window and prompt you to provide the password to secure the pgAdmin interface.
Next, on the top-Left side. Select servers and click on PostgreSQL. This will prompt you to provide the password for the postgres user. Enter the password we set during the PostgreSQL installation.
Once connected to the PostgreSQL server, you will get to the dashboard with all the databases that the postgres user has access to.
Congratulations, you have successfully installed PostgreSQL server and the access it via pgAdmin4
Next steps
If you wish to learn more about PostgreSQL or SQL in general, search SQL on the website and explore more.
If you enjoy our content, please consider buying us a coffee to support our work:
Published by
A highly-skilled software engineer with a passion for teaching and sharing knowledge. From development to hardware, get articles with
high technical accuracy, clarity, and an engaging writing style.
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с PostgreSQL и использованию ее в разработке и тестовых проектах.
Содержание:
- Установка PostgreSQL 11 в Windows 10
- Доступ к PostgreSQL по сети, правила файерволла
- Утилиты управления PostgreSQL через командную строку
- PgAdmin: Визуальный редактор для PostgresSQL
- Query Tool: использование SQL запросов в PostgreSQL
Установка PostgreSQL 11 в Windows 10
Для установки PostgreSQL перейдите на сайт https://www.postgresql.org и скачайте последнюю версию дистрибутива для Windows, на сегодняшний день это версия PostgreSQL 11 (в 11 версии PostgreSQL поддерживаются только 64-х битные редакции Windows). После загрузки запустите инсталлятор.
В процессе установки установите галочки на пунктах:
- PostgreSQL Server – сам сервер СУБД
- PgAdmin 4 – визуальный редактор SQL
- Stack Builder – дополнительные инструменты для разработки (возможно вам они понадобятся в будущем)
- Command Line Tools – инструменты командной строки
Установите пароль для пользователя postgres (он создается по умолчанию и имеет права суперпользователя).
По умолчание СУБД слушает на порту 5432, который нужно будет добавить в исключения в правилах фаерволла.
Нажимаете Далее, Далее, на этом установка PostgreSQL завершена.
Доступ к PostgreSQL по сети, правила файерволла
Чтобы разрешить сетевой доступ к вашему экземпляру PostgreSQL с других компьютеров, вам нужно создать правила в файерволе. Вы можете создать правило через командную строку или PowerShell.
Запустите командную строку от имени администратора. Введите команду:
netsh advfirewall firewall add rule name="Postgre Port" dir=in action=allow protocol=TCP localport=5432
- Где rule name – имя правила
- Localport – разрешенный порт
Либо вы можете создать правило, разрешающее TCP/IP доступ к экземпляру PostgreSQL на порту 5432 с помощью PowerShell:
New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432
После применения команды в брандмауэре Windows появится новое разрешающее правило для порта Postgres.
Совет. Для изменения порта в установленной PostgreSQL отредактируйте файл postgresql.conf по пути C:\Program Files\PostgreSQL\11\data.
Измените значение в пункте
port = 5432
. Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
- Запустите командную строку.
Совет. Перед запуском СУБД, смените кодировку для нормального отображения в русской Windows 10. В командной строке выполните:
chcp 1251 - Перейдите в каталог bin выполнив команду:
CD C:\Program Files\PostgreSQL\11\bin
Основные команды PostgreSQL:
PgAdmin: Визуальный редактор для PostgresSQL
Редактор PgAdmin служит для упрощения управления базой данных PostgresSQL в понятном визуальном режиме.
По умолчанию все созданные базы хранятся в каталоге base по пути C:\Program Files\PostgreSQL\11\data\base.
Для каждой БД существует подкаталог внутри PGDATA/base, названный по OID базы данных в pg_database. Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги. Каждая таблица и индекс хранятся в отдельном файле.
Для резервного копирования и восстановления лучше использовать инструмент Backup в панели инструментов Tools. Для автоматизации бэкапа PostgreSQL из командной строки используйте утилиту pg_dump.exe.
Query Tool: использование SQL запросов в PostgreSQL
Для написания SQL запросов в удобном графическом редакторе используется встроенный в pgAdmin инструмент Query Tool. Например, вы хотите создать новую таблицу в базе данных через инструмент Query Tool.
- Выберите базу данных, в панели Tools откройте Query Tool
- Создадим таблицу сотрудников:
CREATE TABLE employee
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);
Id — номер сотрудника, которому присвоен ключ SERIAL. Данная строка будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу. В следующих строках записаны имя, фамилия сотрудника и его электронный адрес, которые имеют тип CHARACTER VARYING(30), то есть представляют строку длиной не более 30 символов. В строке — Age записан возраст, имеет тип INTEGER, т.к. хранит числа.
После того, как написали код SQL запроса в Query Tool, нажмите клавишу F5 и в базе будет создана новая таблица employee.
Для заполнения полей в свойствах таблицы выберите таблицу employee в разделе Schemas -> Tables. Откройте меню Object инструмент View/Edit Data.
Здесь вы можете заполнить данные в таблице.
После заполнения данных выполним инструментом Query простой запрос на выборку:
select Age from employee;
Learn the steps to install the popular pgAdmin on Windows 11 or 10 to manage local or remote PostgreSQL Database on your system directly.
Like popular phpMyAdmin to graphically manage MySQL or MariaDB databases, we have pgAdmin. It is also a free and open source software that provides a graphical interface to develop and administrators PostgreSQL databases. The open-source license of pgAdmin is inherited from the PostgreSQL project. pgAdmin runs on Windows, Linux, macOS, and other Unix derivatives.
However, unlike Linux, pgAdmin on Windows and macOS can easily be installed to manage remote or locally running PostgreSQL.
Well, pgAdmin offers two types of installation: Desktop Deployment and Server Deployment.
Server Deployment: In this installation method, the pgAdmin is installed as a web application with the help of a web server on a command line server, so that single or multiple users can access it simultaneously using a web browser. Of course, credentials are required for security.
Desktop Deployment: This installation is like any other software installed locally on the computer. This means you don’t require any browser to connect and access PostgreSQL but are limited to only one user currently logged on to the operating system. Also, the user needs a GUI running a supported OS.
Installing pgAdmin as a central web application in the local network (server deployment) is helpful if you don’t want to restrict it to only one PC. However, as we are using Windows here, most of this OS users would like to have a Desktop Deployment only. This tutorial will discuss how to install the pgAdmin App and connect it with a remote PostgreSQL Database server.
The steps will be the same for Windows 10 and the latest one, 11. Users can follow this tutorial even with old OS versions not supported by Microsoft, such as Windows 7.
#1st Way is using the Winget command line
1. Open Windows Terminal or Powershell
As our first method involves the command line to install pgAdmin on Windows 11 or 10, we have to open Windows Powershell first. Right-click on your Windows Start button and select PowerShell (Admin) in Windows 10, whereas Windows Terminal (Admin) in Windows 11.
2. Install pgAdmin 4 using Winget
Like Linux, since Windows 10, Microsoft also offers a package manager by default on its operating systems called “Winget.” Although the list of packages to install using it is not as comprehensive as Linux package managers, yet enough to get some popular software quickly. The good thing is the latest version of pgAdmin is available through it. Hence, let’s run a single command to get pgAdmin 4.
To check the pgAdmin version available via Winget
winget search pgAdmin
To install:
winget install pgAdmin
After this, jump to Step 5 of this article.
#2nd method using the official pgAdmin executable setup…
2. Download pgAdmin
The official website of pgAdmin offers an executable file to install this PostgreSQL’s GUI interface app easily. Hence, visit it using the given link and download the same on your Windows 11 or 10 system. You will have the list of the supported versions; select the latest one, and download the same.
3. Run the Setup
Once you have the setup on your system, double-click on it to run the same, and click on Install for me only (recommended); however, if you want to install pgAdmin for all users’ accounts on your system, then you can for the second option.
4. Install pgAdmin on Windows 11 or 10
Start the installation by clicking the Next button and then Accepting the license. After that, again click the Next buttons to accept the default setting until the setup starts the installation process. Soon, the installation will be completed; confirm it by pressing “Finish.”
5. Launch the pgAdmin
To start the application or GUI interface app for PostgreSQL Database, go to your Windows search and type pgAdmin; as its icon appears, click to run the same.
After the start, you will be asked to assign a master password. Remember this is used to store the sensitive connection data to your databases in encrypted form. The master password is not stored anywhere, so you must remember it well.
6. Connect pgAdmin to Remote PostgreSQL Server
To establish a connection with your remote PostgreSQL Database, click on Add New Server.
In this dialog box, you enter a name for your server to identify it later quickly. It can be the actual name of the remote server or whatever you want to call it on pgAdmin.
Here we are going for a TCP connection, and for that, click on the Connection Tab. After that, you have two options: Configure a direct link to the server via TCP or an indirect reference to the server via SSH tunnel. You can choose the one you want.
Now, in the Connection tab, enter the Ip-address or domain name of the server on which your PostgreSQL instance is installed.
Leave the Port and Maninatance Database values as they are, and add the PostgreSQL instance Database Username and Password you want to access on pgAdmin.
Next, click on the Save button to let the program establish a connection.
Finally, you will have the graphical user interface with PostgreSQL Database to create, delete and edit tables, including other tasks such as backup and restore.
Other Articles:
⇒ MySQL GUI Tools for Windows and Ubuntu/Linux: Top 8 free
⇒ Install Top 10 essential software on Windows 11 using the command
⇒ How to install XAMPP on Windows 10 using the Command prompt
⇒ How to Install Sensu Go Monitoring on Windows
⇒ How to install Ubuntu 22.04 on Windows 11 WSL…
Pre-compiled and configured installation packages for pgAdmin 4 are available
for a number of desktop environments; we recommend using an installer
whenever possible.
In a Server Deployment, the pgAdmin application is deployed behind a webserver
or with the WSGI interface.
If you install pgAdmin in server mode, you will be prompted to provide a role
name and pgAdmin password when you initially connect to pgAdmin. The first
role registered with pgAdmin will be an administrative user; the
administrative role can use the pgAdmin User Management dialog to create
and manage additional pgAdmin user accounts. When a user authenticates
with pgAdmin, the pgAdmin tree control displays the server definitions
associated with that login role.
In a Desktop Deployment, the pgAdmin application is configured to use the
desktop runtime environment to host the program on a supported platform.
Typically, users will install a pre-built package to run pgAdmin in desktop
mode, but a manual desktop deployment can be installed and though it is more
difficult to setup, it may be useful for developers interested in understanding
how pgAdmin works.
It is also possible to use a Container Deployment of pgAdmin, in which Server
Mode is pre-configured for security.
- Deployment
- The config.py File
- Desktop Deployment
- Server Deployment
- Container Deployment
- Login Page
- Recovering a Lost Password
- Avoiding a bruteforce attack
- Enabling two-factor authentication (2FA)
- About two-factor authentication
- Setup two-factor authentication
- Configure two-factor authentication
- User Management Dialog
- Change Ownership Dialog
- Change User Password Dialog
- Lock/Restore Account
- Enabling LDAP Authentication
- Enabling Kerberos Authentication
- Keytab file for HTTP Service
- Apache HTTPD Configuration
- Browser settings to configure Kerberos Authentication
- PostgreSQL Server settings to configure Kerberos Authentication
- Master Password
- Enabling OAUTH2 Authentication
- Redirect URL
- Master Password
- Login Page
- Enabling Webserver Authentication
- Master Password
Note
Pre-compiled and configured installation packages are available for
a number of platforms. These packages should be used by end-users whereever
possible — the following information is useful for the maintainers of those
packages and users interested in understanding how pgAdmin works.
The pgAdmin 4 client features a highly-customizable display that features
drag-and-drop panels that you can arrange to make the best use of your desktop
environment.
The tree control provides an elegant overview of the managed servers, and the
objects that reside on each server. Right-click on a node within the tree control
to access context-sensitive menus that provide quick access to management tasks
for the selected object.
The tabbed browser provide quick access to statistical information about each
object in the tree control, and pgAdmin tools and utilities (such as the Query
tool and the debugger). pgAdmin opens additional feature tabs each time you
access the extended functionality offered by pgAdmin tools; you can open, close,
and re-arrange feature tabs as needed.
Use the Preferences dialog to customize the content and behaviour of the pgAdmin
display. To open the Preferences dialog, select Preferences from the File menu.
Help buttons in the lower-left corner of each dialog will open the online help
for the dialog. You can access additional Postgres help by navigating through
the Help menu, and selecting the name of the resource that you wish to open.
You can search for objects in the database using the Search objects
- User Interface
- Menu Bar
- The File Menu
- The Object Menu
- The Tools Menu
- The Help Menu
- Toolbar
- Tabbed Browser
- Tree Control
- Preferences Dialog
- The Browser Node
- The Dashboards Node
- The Debugger Node
- The ERD Tool Node
- The Graphs Node
- The Miscellaneous Node
- The Paths Node
- The Query Tool Node
- The Schema Diff Node
- The Storage Node
- Keyboard Shortcuts:
- Main Browser Window
- Dialog Tabs
- Property Grid Controls
- SQL Editors
- Query Tool
- Debugger
- ERD Tool
- Inner Tab and Panel Navigation
- Access Key
- Search objects
Before using pgAdmin to manage objects that reside on a server, you must define a
connection to the server; for more information please see Connecting to a Server
in the next section.
In this article, we will see how to install PostgreSQL and pgAdmin on windows 11 step by step.
1. Go to PostgreSQL Download Page
2. Click on Download.
3. Once download is completed, go to the download folder and double click on it.
4. Click on next.
5. Provide the installation directory and click on next.
6. Select the components to install and click on next.
7. Then provide path to store PostgreSQL data and click on next.
8. Provide the password for superuser i.e. postgres and click on next.
9. Default port is 5432. We are not changing it. Click on next.
10. Select locale setting from the drop down and click on next.
11. Review the installation summary and click on next.
12. If we want to launch stack builder then we have to keep checkbox. Otherwise uncheck it and click on finish.
13. Now click on windows button from your keyboard and search for psql to open psql shell to connect to PostgreSQL server on windows 11.
14. Now, we have to provide the hostname, database name, port number and password user postgres to connect to PostgreSQL server. Here I have not given anything except password. Remaining are defaults and press enter.
15. Now, run the following command to check PostgreSQL server installed version.
select version();
Now connect PostgreSQL server using pgadmin:
1. Windows button from your keyboard and search for pgadmin.
2. Provide password and expand servers from left pane. By default, localhost is added. If not added click on Add New Server.
How to start stop PostgreSQL Server on Windows 11
1. Windows+Run from your keyboard then type services.msc.
2. Scroll down postgresql service and right click on it to restart, start or stop PostgreSQL.
So, in this article we have seen how to download and install PostgreSQL latest version on Windows 11 operating system. Then, we have seen how to connect PostgreSQL using psql and pgadmin tool on Windows 11. Finally, we have seen how to stop, start and restart PostgreSQL server on Windows 11.