How to Setup PostgreSQL in XAMPP for Windows

출처: How to Setup PostgreSQL in XAMPP for Windows - Neutron Dev

How to Setup PostgreSQL in XAMPP for Windows

BY CAROL PELU · PUBLISHED OCTOBER 9, 2023 · UPDATED OCTOBER 20, 2023

 

If you’re here, it means you’re already aware that XAMPP includes MariaDB right from the start.

But, if you’re interested in setting up another database management system, like PostgreSQL, you’ll need to go through the installation and configuration process yourself.

Don’t worry, though; that’s precisely what I’ll guide you through in this article.

By the time you finish this step-by-step guide, you’ll have PostgreSQL up and running smoothly in your XAMPP environment for Windows.

You’ll also know how to start and stop the service whenever you need to.

You can still follow this guide even if you don’t use XAMPP.

That’s because we’re not interfering with XAMPP but with the PHP within it.

So if you have your own PHP environment set up, you’re already good to go.

Moreover, you’ll also be introduced to pgAdmin; the most popular open-source administration tool for PostgreSQL.

Let’s get started!

 

Contents  hide 

1. Prerequisites

2. Installing PostgreSQL

3. Configure PHP

4. Check PostgreSQL connection

5. Start & Stop PostgreSQL

6. Connect to local database with pgAdmin

6.1. Comments

Prerequisites

Certainly, to follow this tutorial make sure to have XAMPP installed, and you are using Windows 10 or 11.

If you are on Linux, you can still find crucial information on how to install and use PostgreSQL, but the steps will be different and I won’t be able to support you. So keep that in mind.

🎥 Visual learner? Check out this step-by-step guide in the video format:

 

Installing PostgreSQL

Download the PostgreSQL installed from here.

Set the installation path to C:\xampp\pgsql\16. 16 is the PostgreSQL version. This is a good practice to avoid getting confused when working with multiple versions.

If you’re going through this guide long after its publication, there has most likely been a newer version of PostgreSQL released, so use that version as the folder name.

The installer also comes with pgAdmin and Command Line Tools which are a great addition to your toolbox.

I recommend you install them as well.

You can however opt out of installing Stack Builder.

For the next two steps, set a password and keep the default port.

Configure PHP

Now we need to get PostgreSQL to talk with PHP.

Navigate to C:\xampp\php folder and open the php.ini file.

Then enable the pdo_sql and pgsql_extensions by removing the preceding ; (semicolon).

Check PostgreSQL connection

It’s time to check if the connection with PostgreSQL works.

If you’re using VS Code, you can install the Code Runner extension which allows you to run the code without starting a local server.

But if you don’t use VS Code or simply don’t like this approach, you can go ahead and run XAMPP and start the Apache server.

Then run this script to test the connection:

	<?php
	var_dump(
	pg_connect("
	host=localhost
	port=5432
	dbname=postgres
	user=postgres
	password=root
	")
	);
	// Returns the PgSQL\Connection object if the connection is successful.
	// Otherwise, returns false
	?>

view rawindex.php  hosted with ❤ by GitHub

It will return the PgSQL\Connection object is connection is successful. Otherwise, it will return false.

Great, the PostgreSQL connection has been established!

Start & Stop PostgreSQL

Now we have a problem.

XAMPP’s UI doesn’t have Start and Stop buttons like it does for MySQL.

So how are we going to start and stop the PostgreSQL service?

Allow me to expand a bit on this.

When you install PostgreSQL on a Windows machine, it installs a service on the system.

This service can be controlled from the Services tool.

You can open this tool by pressing Win + R and then type in services.msc

There you’ll find a list of all the services that are available on your Windows machine, including the PostgreSQL service.

By default, the service runs at start-up, meaning that every time you start your machine, the PostgreSQL service starts as well.

If you are okay with this, you don’t need to make any changes.

Personally, I don’t want PostgreSQL to start with Windows, so I keep the status set to Manual. This way, I can start and stop it whenever I want.

To start and stop the PostgreSQL service, you can right-click on it and then select Start and Stop.

But I find it easier to run the following commands:

net start postgresql-x64-16

net stop postgresql-x64-16

Connect to local database with pgAdmin

Watch the video above to get a detailed walkthrough on how to use pgAdmin to connect to the database, run queries, and view data.

 

 

CREATE EXTENSION pg_trgm;

    CREATE USER drupal WITH password 'PASSWORD';
    CREATE DATABASE drupal OWNER drupal;
    GRANT ALL privileges ON DATABASE drupal TO drupal;