How to start and stop PostgreSQL database in windows
In this post, going to explaining how to start and stop the PostgreSQL database in windows. PostgreSQL starts and stop is different from the environment to the environment.
Open Windows Services
Press Windows+R, and type the services.msc and then you can find the PostgreSQL server service name and there you can not find the start and stop options.
So that you need to take the service name here and execute the start and stop in the command prompt
Here PostgreSql server service name is postgresql-x64-13
Start PostgreSQL Server
Open the command prompt through the admin user and then execute the following command
Open Admin Command Prompt
- Go to start
- Type command prompt in the search box
- Right-click on the command prompt
- Select Run as Administrator
And then execute the following command
Syntax:
C:\WINDOWS\system32>net start <service-name>
Example
C:\WINDOWS\system32>net start postgresql-x64-13
postgresql-service-start
Stop PostgreSQL Server
Open the command prompt through the admin user and then execute the following command
Open Admin Command Prompt
- Go to start
- Type command prompt in the search box
- Right-click on the command prompt
- Select Run as Administrator
And then execute the following command
Syntax:
C:\WINDOWS\system32>net stop <service-name>
Example
C:\WINDOWS\system32>net stop postgresql-x64-13
postgresql-service-stop
Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in the IT industry for more than 12 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting, Website Development, and IAM(ForgeRock) Specialist
I am a self-learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.
Инцидент: в ситуации, когда сервер был выключен аварийно, через кнопку выключения или при отсутствии электропитания, то после его включения служба PostgreSQL в некоторых случаях не запускается.
Для версий PostgreSQL:
- 9.X
- 11.X
Некорретное завершение работы службы
Как исправить:
1. Запустите сеанс командной строки от Администратора.
2. Выполните последовательно следующие команды и внимательно следите за их работой.
3. Определить домашний каталог PostgreSQL.
set PGDATA=%SET_POSTGRES_BIN%/../data/
4. Проверьте реальный статус экземпляра службы PostgreSQL.
%SET_POSTGRES_BIN%/pg_ctl.exe status
5. Выполните команду для полной остановки процесса PostgreSQL.
Следующими командами выполняется корректный выход из рабочего состояния сервера СУБД и его запуск.
%SET_POSTGRES_BIN%/pg_ctl.exe stop -m fast
6. Запустите приложение СУБД.
%SET_POSTGRES_BIN%/pg_ctl.exe start
7. После этого заново остановите процесс. Повтор данного шага вызван тем, что таким образом запуска приложение сервера СУБД корректно завершит недостающие транзакции.
%SET_POSTGRES_BIN%/pg_ctl.exe stop -m fast
8. После выполненных шагов по перезапуску и правильной остановке экземпляра СУБД запустите службу PostgreSQL.
net start postgresql-x64-9.4
net start postgresql-x64-11
Служба не запускается. Есть сообщения об ошибках. Отсутствуют исполняемые файлы и DLL-библиотеки СУБД
В некоторых случаях после аварийной перезагрузки или в результате срабатывания антивирусных программ при запуске ОС Windows несколько файлов, которые необходимы для работы СУБД PostgreSQL могут отсутствовать. Это может объясняться критическим сбоем ОС.
При попытке использовать способ для запуска служб и инициирования процесса pg_ctl.exe, будет отображаться сообщение о его отсутствии или недостающих файлов библиотек.
1. Запустите скрипт, с помощью которого, проверьте, что для данной версии СУБД присутствуют все компоненты и файлы, которые входят в состав.
2. Скачайте и разместите файл скрипт в папку с PostgreSQL: {Disk}:/Папка_PostgreSQL/bin/.
- PostgreSQL 11: check_v11.bat.
- PostgreSQL 9: check_v9.bat.
3. Запустите файл скрипта. В результате выполнения будет сформирован файл отчета report.txt.
4. Откройте файл отчета и проверьте, что все компоненты присутствуют.
Обязательно должны присутствовать такие библиотеки и исполняемые файлы, а также все библиотеки DLL.
- libintl-9.dll
- pg_ctl.exe
- postgres.exe
- psql.exe
5. Если какие-либо файлы отсутствуют. Тогда загрузите архив для соответствующей версии PostgreSQL и скопируйте недостающие файлы в папку СУБД {Disk}:/Папка_PostgreSQL/bin/.
- Сборник файлов для PostgreSQL 11
- Сборник файлов для PostgreSQL 9
6. После копирования недостающих файлов:
- Остановите службы сервера приложений SetRetail10 и МУК.
- Запустите службу PostgreSQL.
- Запустите службы сервера приложений SetRetail10 и МУК.
Дополнительная информация
Сравнительная таблица списка исполняемых файлов в версиях PostgreSQL
| PostgreSQL 9.4 | PostgreSQL 11.7 |
| clusterdb.exe | clusterdb.exe |
| createdb.exe | createdb.exe |
| createlang.exe | createuser.exe |
| createuser.exe | dropdb.exe |
| dropdb.exe | dropuser.exe |
| droplang.exe | ecpg.exe |
| dropuser.exe | icudt53.dll |
| ecpg.exe | icuin53.dll |
| iconv.dll | icuio53.dll |
| initdb.exe | icule53.dll |
| isolationtester.exe | iculx53.dll |
| libeay32.dll | icutest53.dll |
| libintl-8.dll | icutu53.dll |
| libpq.dll | icuuc53.dll |
| libxml2.dll | initdb.exe |
| libxslt.dll | isolationtester.exe |
| oid2name.exe | libcrypto-1_1-x64.dll |
| pg_archivecleanup.exe | libcurl.dll |
| pg_basebackup.exe | libcurl.lib |
| pg_config.exe | libecpg.dll |
| pg_controldata.exe | libecpg_compat.dll |
| pg_ctl.exe | libiconv-2.dll |
| pg_dump.exe | libintl-9.dll |
| pg_dumpall.exe | libpgtypes.dll |
| pg_isolation_regress.exe | libpq.dll |
| pg_isready.exe | libssl-1_1-x64.dll |
| pg_receivexlog.exe | libwinpthread-1.dll |
| pg_recvlogical.exe | libxml2.dll |
| pg_regress.exe | libxslt.dll |
| pg_regress_ecpg.exe | oid2name.exe |
| pg_resetxlog.exe | pg_archivecleanup.exe |
| pg_restore.exe | pg_basebackup.exe |
| pg_standby.exe | pg_config.exe |
| pg_test_fsync.exe | pg_controldata.exe |
| pg_test_timing.exe | pg_ctl.exe |
| pg_upgrade.exe | pg_dump.exe |
| pg_xlogdump.exe | pg_dumpall.exe |
| pgAdmin3.exe | pg_isolation_regress.exe |
| pgbench.exe | pg_isready.exe |
| postgres.exe | pg_receivewal.exe |
| psql.exe | pg_recvlogical.exe |
| reindexdb.exe | pg_regress.exe |
| ssleay32.dll | pg_regress_ecpg.exe |
| stackbuilder.exe | pg_resetwal.exe |
| vacuumdb.exe | pg_restore.exe |
| vacuumlo.exe | pg_rewind.exe |
| wxbase28u_net_vc_custom.dll | pg_standby.exe |
| wxbase28u_vc_custom.dll | pg_test_fsync.exe |
| wxbase28u_xml_vc_custom.dll | pg_test_timing.exe |
| wxmsw28u_adv_vc_custom.dll | pg_upgrade.exe |
| wxmsw28u_aui_vc_custom.dll | pg_verify_checksums.exe |
| wxmsw28u_core_vc_custom.dll | pg_waldump.exe |
| wxmsw28u_html_vc_custom.dll | pgbench.exe |
| wxmsw28u_stc_vc_custom.dll | postgres.exe |
| wxmsw28u_xrc_vc_custom.dll | psql.exe |
| zic.exe | reindexdb.exe |
| zlib1.dll | stackbuilder.exe |
| vacuumdb.exe | |
| vacuumlo.exe | |
| wxbase28u_net_vc_custom.dll | |
| wxbase28u_vc_custom.dll | |
| wxbase28u_xml_vc_custom.dll | |
| wxmsw28u_adv_vc_custom.dll | |
| wxmsw28u_aui_vc_custom.dll | |
| wxmsw28u_core_vc_custom.dll | |
| wxmsw28u_html_vc_custom.dll | |
| wxmsw28u_xrc_vc_custom.dll | |
| zic.exe | |
| zlib1.dll |
PostgreSQL is an advanced, freely available, and highly stable relational database management system that offers numerous features, such as accuracy, integrity, resilience, etc. The Postgres database is widely used for storing data of web apps, mobile apps, analytical apps, etc. However, to attain any Postgres features, you must know how to start, stop, or restart a Postgres Server.
To tackle such scenarios, Postgres offers different methods, such as the “pg_ctl” utility, “services” manager, etc. This post presents a practical guide on how to start, stop, or restart the PostgreSQL server on the Windows Operating System.
How Do I Start the Postgres Server?
There are various ways to start the Postgres server on Windows, such as using the “net start” command, “pg_ctl” utility, or “services” manager.
Method 1: Starting Postgres Server Using “net start”
Launch the Windows CMD as an administrator and execute the “net start” command to start the Postgres Server:
net start postgresql-x64-15
Method 2: Starting Postgres Server Using “pg_ctl”
Firstly, you need to find the directory’s path where Postgres is located. If you didn’t change the default path while installing Postgres, then it must be located in the “Program Files” directory inside the “C” drive.
The complete path will look something like this: “C:\Program Files\PostgreSQL\15\data”:
Once you find the complete path, open the CMD and execute the following command to “start the Postgres Server”:
pg_ctl -D "C:\Program Files\PostgreSQL\15\data" start
Note: Windows Path for Postgres tools must be set to get the error-free output. Else you will encounter a “not recognized as an internal/external command” error.
Method 3: Starting Postgres Server Using Services Manager
Press the “win” key + “R” to launch the “Run” window. Type the “services.msc” and hit the “OK” button to open the Services Manager:
In the “Services Manager”, search for “Postgresql-x64-15”, select the service, and hit the “Start/play” button to start a Postgres server via the “services” manager:
Once you press the “start” button the service’s status will be changed to “running”:
How to Stop the Postgres Server on Windows?
A Postgres server can be stopped using the “net stop” command, the “pg_ctl” utility, or the “services” manager.
Method 1: Stopping the Postgres Server Using “net stop”
Execute the below-mentioned command from the Command prompt to stop the Postgres Server:
net stop postgresql-x64-15
Method 2: Stopping the PostgreSQL Server via the “pg_ctl”
Users may use the “pg_ctl” utility to stop the Postgres server:
pg_ctl -D "C:\Program Files\PostgreSQL\15\data" stop
Method 3: Stopping the Postgres Server Using the Services Manager
Open the “Services Manager”, search for “Postgresql-x64-15”, select the service, and hit the “Stop” button to stop a Postgres server via the “services” manager:
Clicking on the “Stop” button will stop the Postgres Server.
Note: Similarly, to pause a Postgres Server on Windows, you can select the “pause” button from the Services manager or execute the “net pause postgresql-x64-15” command from the command prompt.
How Do I Restart the Postgres Server on Windows?
You can restart the Postgres server on the windows operating system using the “Services” Manager and “pg_ctl” utility.
Method 1: Restarting the Postgres Server via the “pg_ctl”
Run the below-given command from the CMD to restart the Postgres Server:
pg_ctl -D "C:\Program Files\PostgreSQL\15\data" restart
Method 2: Restarting the Postgres Server Using the Services Manager
Launch the “Services Manager”, locate the “Postgresql-x64-15”, select the desired service, and hit the “restart” button to restart a Postgres server via the “services” manager:
Clicking on the Restart button will restart the Postgres Server.
Conclusion
There are various ways to start, stop, or restart the Postgres server on Windows, such as using the “net start” command, “pg_ctl” utility, or “services” manager. To get the error-free output, Windows Path for Postgres tools must be set. Else you will encounter a “not recognized as an internal/external command” error. This post presented a practical guide on how to start, stop, or restart the PostgreSQL server on the windows operating system.
Краткое руководство по установке и первоначальной настройке системы управления базами данных PostgreSQL на ОС Windows Server 2019 Core.
В первую очередь запускаем PowerShell и подключаем модуль BitsTransfer для скачивания файлов
Import-Module -Name BitsTransfer
После этого на диске C создадим каталог Distr и перейдём в него
cd \ mkdir Distr cd \Distr
Затем получаем ссылку для скачивания дистрибутива со страницы https://enterprisedb.com/software-downloads-postgres
и скачиваем с помощью команды
Start-BitsTransfer -Source https://get.enterprisedb.com/postgresql/postgresql-14.1-1-windows-x64.exe
Запускаем установщик
postgresql-14.1-1-windows-x64.exe
выбираем директорию, в которую будет установлена СУБД
выбираем компоненты, которые будут установлены
выбираем директорию, где будут храниться базы данных
задаём пароль пользователю postgres
оставляем порт по умолчанию — 5432
выбираем локаль: Russian, Russia
проверяем заданные для установки параметры и компоненты
должно быть всё готово для установки
и после клика по кнопки Next начнётся установка
также по желанию можно установить Stack Builder (но не обязательно)
После установки можно запустить Диспетчер задач
taskmgr
перейти во вкладку Службы и появится служба postgresql-x64-14
Для запуска консоли PostgreSQL необходимо перейти в каталог C:\Program Files\PostgreSQL\14\bin и в нём запустить утилиту psql с функцией DBA:
C:\Program Files\PostgreSQL\14\bin > psql -U postgres
Для того, чтобы иметь возможность подключаться к серверу БД удалённо, необходимо перейти в каталог и в нём открыть файл pg_hba.conf
C:\Program Files\PostgreSQL\14\data > notepad pg_hba.conf
здесь необходимо будет добавить строчку:
host all all all md5
Ещё на всякий случай в файле postgresql.conf, который находится там же,где и pg_hba.conf, проверим наличие строчки:
listen_addresses = "*"
если в отличии от звёздочки стоит другое значение, значит исправляем на звёздочку.
Для удалённого подключения к серверу в брандмауэре с помощью консоли необходимо открыть порт 5432/TCP для входящих соединений
# netsh advfirewall firewall add rule name="PostgreSQL" protocol=TCP localport=5432 dir=IN action=allow
Чтобы новые параметры вступили в силу, необходимо перезапустить службу postgresql-x64-14, либо перезагрузить машину.
-
Basic
PG_CTLInitialization Commands in Windows -
Use
SERVICES.MSCtoSTART/STOPa PostgreSQL Session in Windows
Today, we will be learning how to start a PostgreSQL server on Windows. In the previous article about downloading and installing PostgreSQL on Windows, we already learned how to start a session, create a USER, issue queries, and work with different database objects.
Our topic now focuses on trying to run a PostgreSQL server session to which other users in a local system can connect.
Basic PG_CTL Initialization Commands in Windows
PG_CTL is a utility used to start and control a PostgreSQL server. PG_CTL is a manual setup for initializing a PostgreSQL session as it provides a controlled exit, encapsulates different tasks, and can be used for various operations related to our database.
To start a server on Windows, go to your command prompt inside the PostgreSQL installation for the BIN folder.
pg_ctl start -D "[\data folder directory]";
This will start your PostgreSQL server. To do this on a different port on your computer on which you want other users to listen, use a query as follows.
pg_ctl -o "-F -p [post_number]" start
Enter your PORT NUMBER in the port_number defined without the brackets. This will help start your server and allow other users to connect to it on different systems.
However, if your PostgreSQL server stopped due to a reason or a slight malfunction, you are better off using any of the following commands to make it work.
pg_ctl restart -D "[\data folder];
pg_ctl start company; --may or may not work / may produce errors
...
Many times, there will be no need to do any of this. Starting PGADMIN, for example, tends to already create a PostgreSQL session on a pre-defined port in your PC, allowing other users to discover it easily.
There may be no need to write any of the commands given above to make your PostgreSQL server boot up.
Specify Paths While Issuing PG_CTL START/RESTART in Windows
Sometimes, calling a simple PG_CTL START/RESTART isn’t the way to go. You may need to specify the DATA folder path within your PostgreSQL installation and then issue the boot commands.
Calling the queries given above, you will get an OUTPUT.
Output:
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2022-04-28 19:28:38.766 PKT [3228] LOG: redirecting log output to logging collector process
2022-04-28 19:28:38.766 PKT [3228] HINT: Future log output will appear in directory "log".
stopped waiting
pg_ctl: could not start server
Examine the log output.
The output given above happens when a PostgreSQL server is already running. Because we already had PGADMIN booted up and running in the background, calling the query above would be unable to initialize a server on the port already being used.
However, doing something below would run separate servers on the same PC.
pg_ctl -o "-F -p 5656" start -D "C:\Program Files\PostgreSQL\14\data"
Output:
waiting for server to start....2022-04-28 19:34:04.588 PKT [11784] LOG: redirecting log output to logging collector process
2022-04-28 19:34:04.588 PKT [11784] HINT: Future log output will appear in directory "log".
done
server started
Similarly, you could also issue the same commands for RESTART.
pg_ctl restart -D "C:\Program Files\PostgreSQL\14\data"
or
pg_ctl -o "-F -p 5656" restart -D "C:\Program Files\PostgreSQL\14\data"
Output:
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-04-28 19:35:46.007 PKT [884] LOG: redirecting log output to logging collector process
2022-04-28 19:35:46.007 PKT [884] HINT: Future log output will appear in directory "log".
done
server started
You can see in the output how the server is first shut down because it is already running, then it is restarted and logged for any changes during its session.
Use SERVICES.MSC to START/STOP a PostgreSQL Session in Windows
We already know that a PostgreSQL server is instantiated on your PC when you launch it, either from PSQL or PGADMIN. We can do a few things in SERVICES.MSC For STOPPED or PAUSED services, the Windows Services Manager.
-
Press Windows+R and type in
SERVICES.MSC. -
Once the Windows Services Manager has opened, search for the PostgreSQL server service in the list. It may be named as follows:
postgresql-x64-14 - PostgreSQL Server 14.
-
Right-click on this to either
START,STOP,PAUSE,RESUME, orRESTARTyour server. You can even change the startup time toAUTOMATICfor it to start by itself the next time.
The Services Manager also tends to tell us the cause of the failure of a specific service. To view the executable path, you can open the General tab and view it as follows.
Initiate the PostgreSQL Server From the COMMAND PROMPT Using NET START
We can use the NET START command to start a service from the command prompt. The syntax is as follows.
You may need to find the SERVICE_NAME for your PostgreSQL session. It is also mentioned in the General tab.
Now you can issue a command as follows.
net start postgresql-x64-14;
This will boot up our server and display output as follows.
Output:
The postgresql-x64-14 - PostgreSQL Server 14 service is starting.
The postgresql-x64-14 - PostgreSQL Server 14 service was started successfully.
However, remember that COMMAND PROMPT needs to be started as an ADMINISTRATOR and not a normal user.
Right-click on the CMD.EXE executable file and click Run as Administrator. Else you will get an error as follows.
Output:
System error 5 has occurred.
Access is denied.
So today, we learned how to start a PostgreSQL server on Windows in different ways. We hope you read through this article carefully and can use it per your needs.
Enjoying our tutorials? Subscribe to DelftStack on YouTube to support us in creating more high-quality video guides. Subscribe