Install PostgreSQL 12 on Windows 10 — for beginners
In addition, we will also install and configure pgAdmin 4, which is a standard and free graphical tool for PostgreSQL DBMS management that we can use for writing SQL queries, developing procedures, functions, as well as for PostgreSQL administration.
WHAT IS POSTGRESQL?
PostgreSQL is a free object-relational database management system (DBMS). PostgreSQL is implemented for many operating systems such as: BSD, Linux, macOS, Solaris and Windows.
PostgreSQL uses the PL/pgSQL language.
PL/pgSQL is a procedural extension of the SQL language, developed and used in PostgreSQL DBMS.
PL/pgSQL language is designed to create functions, triggers, it adds control structures to SQL language, and it helps us to perform complex calculations.
PostgreSQL is one of the most popular database management systems (TOP 5 popular database management systems).
At the time of writing, the most up-to-date version of PostgreSQL 12 is the one we will be installing.
SYSTEM REQUIREMENTS FOR POSTGRESQL 12 INSTALLATION ON WINDOWS
PostgreSQL 12 can not be installed on all versions of Windows, in particular, the following versions are officially supported and only 64-bit:
- Windows Server 2012 R2;
- Windows Server 2016;
- Windows Server 2019.
As you can see, there’s no Windows 10 on the official list, but installation on this system goes smoothly, as does subsequent PostgreSQL functioning.
In addition, there are other requirements:
- Processor with at least 1 GHz;
- 2 gigabytes of RAM;
- At least 512 megabytes of free disk space (more for installing additional components is recommended);
- It is also recommended that all Windows operating system updates be installed.
INSTALLING POSTGRESQL 12 AND PGADMIN 4 ON WINDOWS 10
So let’s move on to the installation process and look at all the steps that need to be followed in order to install PostgreSQL 12 and pgAdmin 4 on Windows 10.
STEP 1 — DOWNLOADING THE INSTALLER FOR WINDOWS
As mentioned earlier, PostgreSQL is implemented for many platforms, but since we will be installing PostgreSQL on Windows, we need a Windows installer accordingly. You can of course download this distribution from the official PostgreSQL website, here is the download page — https://www.postgresql.org/download/windows/.
After going to the page you need to click on the link “Download the installer”, as a result you will be taken to the site of EnterpriseDB, which prepares graphical distributions of PostgreSQL for many platforms, including Windows, so you can immediately go to this site, here is the link to the download page https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.
Here you need to select the PostgreSQL version and platform, in our case choose PostgreSQL 12 and Windows x86–64.
As a result, you should download the file postgresql-12.2–2-windows-x64.exe with the size of about 191 Mbytes (version 12.2–2 is available at the time of writing).
STEP 2 — STARTING THE POSTGRESQL INSTALLER
Now in order to start the installation you need to run the downloaded file (PostgreSQL installation requires administrator rights).
After the launch, a welcome screen will open, click “Next”.
STEP 3 — SPECIFY THE DIRECTORY TO INSTALL POSTGRESQL 12
Next, if necessary, we can specify the path to the directory where we want to install PostgreSQL 12, but we can leave it by default. Click “Next”.
STEP 4 — CHOOSE COMPONENTS FOR INSTALLATION
Then select the components that we need to install, to do this we leave the checkboxes for the components that we need, and be sure we need PostgreSQL Server and pgAdmin 4.
You can install the command line utilities and Stack Builder on your own, i.e. you can not install them. Press “Next.”
STEP 5 — SPECIFY A DIRECTORY TO STORE DATABASE FILES
On this step, we need to specify the directory where the database files will be located by default.
In the case of a test installation, for example, for training, you can leave it by default, but “combat” databases should always be stored in a separate location, so if you plan to use the PostgreSQL server for some other purposes, it is better to specify a separate disk.
Click “Next”.
STEP 6 — SET A PASSWORD FOR THE POSTGRES SYSTEM USER
Next we need to set a password for a postgres user — this is a PostgreSQL Server administrator with maximum rights.
We enter and confirm the password. Click “Next”.
STEP 7 — SPECIFY THE PORT FOR YOUR POSTGRESQL INSTANCE
On this step, if necessary, we can change the port on which PostgreSQL Server will work, if you do not have such a need, then leave it by default.
Click “Next”.
STEP 8 — SPECIFY THE ENCODING IN THE DATABASE
Then we can specify a specific data encoding in the database, for this purpose we need to select the desired Locale from the drop-down list.
However, you can also leave it by default by clicking “Next”.
STEP 9 — CHECKING POSTGRESQL INSTALLATION PARAMETERS
Everything is ready for installation, at this step we check the parameters we entered earlier and if everything is correct, i.e. everything we entered, we press “Next”.
STEP 10 — START THE INSTALLATION PROCESS
Next you will see another additional window where we must click “Next” to start the PostgreSQL installation process on your computer.
The installation has begun, it will last literally one minute.
STEP 11 — FINISHING THE INSTALLATION
When the “Completing the PostgreSQL Setup Wizard” message window is displayed, the installation of PostgreSQL 12, pgAdmin 4 and other components will be completed.
Also in this window we will be offered to run Stack Builder to download and install additional components, if you do not need it, then uncheck “Lanch Stack Builder at exit?
Click “Finish”.
RUN AND CONFIGURE PGADMIN 4
PostgreSQL 12 and pgAdmin 4 we have installed, now let’s start pgAdmin 4, connect to the server and configure the pgAdmin work environment.
To start pgAdmin 4, go to the start menu, find PostgreSQL 12 and pgAdmin 4 in it.
CONNECTING TO THE POSTGRESQL 12 SERVER
pgAdmin 4 has a web interface, so as a result you should run your browser and open the pgAdmin 4 application.
The first time you start pgAdmin 4, you will see a “Set Master Password” window, where we must set a “master password”, this may not be done, but if we save the user’s password (“Save password” checkbox), for example, so that every time you connect you do not need to enter it, it is strongly recommended to invent and specify an additional password here, it is done once.
Enter it and click “OK”.
To connect to the newly installed local PostgreSQL server in the server browser, click on “PostgreSQL 12”.
The “Connect to Server” window will start, where you need to enter the password of the postgres system user, i.e. this is the password that you invented when you installed PostgreSQL.
Enter the password, tick “Save Password” to save the password and each time you do not enter it (thanks to the “master password” functionality, all passwords saved in this way will be additionally encrypted).
Click on “OK”.
As a result you will connect to the local PostgreSQL 12 server and see all the objects that are located on this server.
EXAMPLE OF WRITING AN SQL QUERY IN QUERY TOOL
In order to make sure that our PostgreSQL server is running, let’s write a simple SELECT query that will show us the PostgreSQL server version.
To write SQL queries in pgAdmin 4, you can use Query Tool or Russian “Query”, you can run it using the icon on the panel or from the menu “Tools”.
After you open the Query Tool, write.
This query shows the PostgreSQL version. As you can see, everything works!
Getting started with PostgreSQL in Windows 10
Download the Version of PostgreSQL that suits your OS. In my case I will choose the installer certified by EnterpriseDB for Windows 64bit.
Start the installer and accept the defaults — only add your own password that you will be able to use with the username: postgres to log in to the pgAdmin Control Panel.
Adding some Dummy Data to work with
We are going to use the free available Consumer Complaint Database from the Consumer Financial Protection Bureau.
Start the pgAdmin4 Admin Panel and right-click Databases to create a new db. Give it a name, e.g. consumer complaints :
Now right-click the newly created Database and selct the Query Tool. Paste the following in and click on Execute to create the consumer_complaint table:
Note: If you are running the Astrill VPN client on your System, this will block access to the Query Tool. Add pgAdmin4 to the list of allowed programs that are not forced through the VPN tunnel.
Now we can add the data from the earlier downloaded ConsumerComplaints.csv file, by executing the following query (change the path-to-file according to your download folder)
We have now created our schema with 18 columns and copied 65499 data entries from the CSV file into our table. We can check our data by running the following query:
Установка и базовая настройка PostgreSQL в Windows 10
30.08.2022
itpro
Windows 10, Windows Server 2016
комментариев 14
PostgreSQL — это бесплатная объектно-реляционная СУБД с мощным функционалом, который позволяет конкурировать с платными базами данных, такими как Microsoft SQL, Oracle. PostgreSQL поддерживает пользовательские данные, функции, операции, домены и индексы. В данной статье мы рассмотрим установку и краткий обзор по управлению базой данных PostgreSQL. Мы установим СУБД PostgreSQL в Windows 10, создадим новую базу, добавим в неё таблицы и настроим доступа для пользователей. Также мы рассмотрим основы управления PostgreSQL с помощью SQL shell и визуальной системы управления PgAdmin. Надеюсь эта статья станет хорошей отправной точкой для обучения работы с 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.
Измените значение в пункте port = 5432 . Перезапустите службу сервера postgresql-x64-11 после изменений. Можно перезапустить службу с помощью PowerShell:
Restart-Service -Name postgresql-x64-11
Более подробно о настройке параметров в конфигурационном файле postgresql.conf с помощью тюнеров смотрите в статье.
Утилиты управления PostgreSQL через командную строку
Рассмотрим управление и основные операции, которые можно выполнять с PostgreSQL через командную строку с помощью нескольких утилит. Основные инструменты управления PostgreSQL находятся в папке bin, потому все команды будем выполнять из данного каталога.
-
Запустите командную строку.
Основные команды PostgreSQL:
- Проверка установленной версии СУБД: psql –V
- Для создания новой базы данных воспользуйтесь утилитой createdb: createdb -U postgres testdb (где postgres суперпользователь, testdb новая база данных)Введите пароль суперпользователя.
- Проверить список активных баз: Psql -U postgres –l (пароль)
- С помощью инструмента createuser cоздадим нового пользователя: createuser –U postgres operator (где operator -имя нового пользователя)
- Предоставим пользователю привилегии суперпользователя (на практике этого делать не надо). Запустите интерактивную командную оболочку управления PostgreSQL (shell): psql –U postgres . С помощью SQL команды ALTER ROLE предоставим нужные права нашему пользователю: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB; . Мы предоставили пользователю права суперпользователя, права на создание ролей и баз данных.
- Для выводы списка пользователей и ролей в СУБД выполните команду: \du
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;
How do I install PostgreSQL on Windows 10?
For having the PostgreSQL server installed on a Windows 10 system, the following steps should be performed on the target machine:
Step # 1: Access the Downloads Page of PostgreSQL:
In this step, you will have to navigate to the Downloads page of the PostgreSQL server. You can access that page by looking for the URL stated below in any web browser of your choice. We have used Google Chrome for this.
Step # 2: Download the Desired Version of PostgreSQL for your Windows 10 System:
When you access that page, you need to select the desired version of the PostgreSQL server relevant to your target machine’s specifications. Once you select a suitable version, you need to click on the respective Download button.
After hitting the Download button, you will be asked to pick a download location.
Once you will do that, the PostgreSQL server will immediately start downloading on your Windows 10 system, and you will be able to see the download status on your screen as shown in the following image:
Step # 3: Access the Directory where the PostgreSQL has been downloaded on your System:
When the PostgreSQL server is downloaded successfully on your Windows 10 system, you must click on the “Show all” option.
When the Downloads page of your browser appears on your screen, you need to locate your PostgreSQL download and then click on the “Show in folder” option. Doing this will take you straight to the directory in which your PostgreSQL server has been downloaded.
Step # 4: Run the Installer of PostgreSQL on your Windows 10 System:
When you reach the PostgreSQL download directory, right-click on your downloaded file and then select the “Run as administrator” option from the pop-up menu.
Step # 5: Proceed further from the Welcome Page of the PostgreSQL Installer:
Performing the step mentioned above will launch the PostgreSQL installer on your screen. Just click on the “Next” button to continue further from this dialogue box.
Step # 6: Select the Installation Directory for PostgreSQL:
Now, you will be asked to select the installation directory for PostgreSQL. It is always good to go on with the default directory provided by your Windows 10 system and click on the “Next” button.
Step # 7: Select all the Components that you want to be installed along with PostgreSQL:
Then, you need to select all the components that you want to be installed with PostgreSQL. All the components presented in this dialogue box will be selected by default. You can even uncheck any of these if you find them irrelevant and then click on the “Next” button.
Step # 8: Select the Storage Directory for PostgreSQL:
After that, you will be asked to select a directory to store your PostgreSQL data. Again, it is recommended to go on with the default directory provided by your Windows 10 system.
Step # 9: Create a Password for the PostgreSQL Database Super User:
Now, you will be asked to create a password of your choice for the PostgreSQL database superuser. You will have to type this password twice for confirmation.
Step # 10: Select a Port on which your PostgreSQL Server should listen:
Then, you will be asked to select a port on which your PostgreSQL server will be allowed to listen. Again, it is advised to go on with the default port selected by your Windows 10 system.
Step # 11: Select a Location for the New Database Cluster:
Now, you need to select a location for your new database cluster. Yet again, you are advised to go on with the default location provided by your Windows 10 system.
Step # 12: Verify your Whole Installation Summary:
Finally, you will be presented with a summary of all the choices you have made in the steps described above. You just need to go through it once to verify it. This is done to go back at this point to fix anything if you selected it incorrectly.
Step # 13: Start the PostgreSQL Installation on your Windows 10 System:
Now, your PostgreSQL installation will begin as soon as you click on the “Next” button.
You will also be presented with the installation progress bar as shown in the following image:
Step # 14: Complete the Installation of PostgreSQL on your Windows 10 System:
Once the PostgreSQL installation completes, you will have to click on the “Finish” button to close the installation dialogue box.
Some Additional Steps:
By now, the PostgreSQL server will be successfully installed on your Windows 10 system. However, to facilitate some enhanced features, you can also perform the additional steps listed below:
Step # 15: Select your PostgreSQL Installation to Install Stack Builder:
As soon as your PostgreSQL installation completes, the Stack Builder installation dialogue box will appear on your screen.
From this dialogue box, you need to select your PostgreSQL installation from the dropdown list and then click on the “Next” button.
Step # 16: Select all the Applications that you want to be installed along with the Stack Builder:
Now, you will be asked to select all the applications that you want to be installed.
Then, you will be asked to review your selection as shown in the following image:
When you will do that, the selected applications will start downloading on your system.
Step # 17: Install all the Selected Files:
Finally, you can install the selected files by clicking on the “Next” button. Once these files are installed, your Windows 10 system will automatically restart.
Launching the PostgreSQL Console on Windows 10:
You can launch the PostgreSQL console on Windows 10 by performing the step explained below:
Step # 18: Accessing the PostgreSQL Console through Windows 10’s Search Bar:
You just need to type “psql” in your Windows 10 search bar, and you will instantly be presented with the SQL Shell (psql) result. By clicking on this result, you will be able to access the PostgreSQL console on your Windows 10 system very conveniently.
Conclusion:
The installation procedure of the PostgreSQL server on a Windows 10 system is much lengthier as compared with the Linux installation. However, the steps are quite simple and straightforward, and we have made sure to convey them effectively to you through this article. Therefore, if you follow these steps correctly, you will be able to install the PostgreSQL server on your Windows 10 system successfully.
About the author
Aqsa Yasin
I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.