How to Create Your Own PostgreSQL Server on Your Own Computer in Windows
In this article, we show how to create your own postgresql server on your own computer in windows.
Thus, you run your own server, your own database server.
You can then connect to using localhost and use for projects such as for django or any other internet project.
This can make you cut down on costs.
Though frameworks such as Django come with a database system, MySQLite, that you can use for non-production purposes, some experts recommend that you use the database system that you will use for production from the very beginning of a project. This way, you don't have to worry about any inconsistencies later when you go to production.
In this article, we show how to set up your own postgresql database server on your own computer using your own current IP address for your network.
If you are not interested in remote connection and only want to use the postgresql server on your local machine, then you don't need to worry about IP addresses.
But if you want to run a server that is always up (assuming you keep your system on at home) and one which you and others can connect to from another network, then you tie it to your server.
A local PostgreSQL needs no internet connection, because you are simply using your own computer (and all the software that's installed on it for the PostgreSQL database server). It's the equivalent to opening up Microsoft Word on your computer, typing in a document, and then saving it to your computer. Of course, for this, no internet connection would be necessary. PostgreSQL or any other database server software functions the same if it's operated locally on a computer. Everything is saved to the computer with no internet connection needed.
Remote connection would require the internet, because it's run from a computer outside that would require the internet in order to be able to save information to a remote computer. It's the equivalent of uploading a video to youtube. For this connection, and other remote connection, you would need internet connection.
So if you are trying to create a database server that allows for remote connection (and not just local operation which means on the computer or laptop you have), then you connect to an IP address.
If the IP address of your network is static (meaning it doesn't change), you can directly us your network's IP address.
If you connect to the internet at home through a WIFI system, you can use the IP address of your WIFI system.
If you connect to the internet at home via ethernet, you use the IP address of this ethernet connection.
You can find your computer's IP address in windows, through the command prompt, typing in the following shown below.
When you run this, you can see your computer's IP address.
You can use the IPv4 address of whatever device you are using (whether Wifi or Ethernet if your computer is hardwired to the internet).
This method only works with static IP addresses.
With dynamic IP addresses, the IP addresses change, so that you cannot take this approach.
Most Wifi systems employ dynamic IP addresses (meaning they change from day to day), so we need to take a different approach.
Instead of directly using an IP address for remote connection, what we should do instead is a service that provides dynamic domain name service (DNS). What this means is that instead of using the IP address (which changes), we instead use a hostname (kind of like a domain name for a website) and the service tracks the changes in the IP address of the network. You just have to download
One such website that offers this service is https://www.noip.com/.
With this website, you get one hostname for free (as of the time of this writing on 5/28/24).
You tie this hostname to any of the dynamic IP addresses generated by your wifi system. And it tracks changes to the IP address.
So now that you have the IP address (for static addresses) or the hostname (for dynamic addresses), the next thing we need to do is download the postgresql software to our computer.
This can be found at the following link: https://www.postgresql.org/download/
This
After installation, you want to make sure that this software is added to your computer's environment variables, so that the postgresql commands such as psql register and run within the command prompt.
So go to command prompt and type in the following shown below.
If your computer doesn't register this command, you must add the entire path to the postgresql software to the environment variable. You add this to the Path of the environment variable.
To the Path variable, you add the path, C:\Program Files\PostgreSQL\16\bin
Then you can close and reopen the command prompt.
Now if you type in psql, you should get some type of psql error and the mention of 'localhost'.
Now we move onto the next step.
Even though you have the postgresql software installed on your computer in this standalone setup and you really don't need anything else, database server softwares are more easily managed with database management software (dbms). Otherwise, you have to rely on just command line interactions instead of having a graphical interface.
pgadmin is the graphical user interface (GUI) that works with postgresql, so that you also have a way to manage your postgresql databases with a GUI.
You can down pgadmin at the following link: pgadmin 4
Once you open pgadmin, it will ask for the password to the server you want to connect to.
The default password is 'postgresql', as is the default user name and database name.
The last thing we want to make sure that we do is to make sure that our postgresql server software is up and running.
To do this in windows, there is a software named services.
You want to make sure that the postgresql software is running.
So you open services and go down to PostgreSQL Server.
This can be seen in the image below.
If you look at the status, you see that it is running.
Therefore, our database server is operational.
If you look at the extended view of this window, you can see that we can stop the service, pause the service, or restart the service.
This can be seen here: PostgreSQL Server in Windows- Extended View
Now we have everything together.
So you have two options of how to implement this database.
If you want this database to only be used for yourself, as local storage on your computer, then you can keep it as localhost and not allow for any remote connection.
If you want the database to be used by others, outside of your network (and your computer), then you have to change from localhost to an IP address. This way, others outside the network with an internet connection can access your database (assuming that you keep your server up and running all the time with internet connection so that it can serve up data from the database requested by others).
A good idea is to go into pgadmin 4 and create a new server group.
You can see this done below.
In this example, the server group name is called Websiteserver.
Once this server group is created, then we can register the server.
This is the important part.
We first create a server name, as shown below.
So this server, we call Mywebsiteserver.
Very important is the Connection tab of the server.
This is shown below.
So let's go over this now.
The first thing is the host name/address. Here if you want to run it just for yourself on your computer, then you specify 'localhost' here.
If you want to allow for remote access (for other computers), then you specify an IP address. Static IP address makes this process easier, because it doesn't change. But dynamic IP addresses can also work, with the use of dynamic name server. This service allows you to use a name instead of an IP address, and the software changes the IP address, as it changes dynamically. This way, it tracks changes to the IP addresses, as they occur.
The port is 5432. This is what postgresql uses.
You can also specify the name of the database. By default, it is 'postgres'.
You can also specify the name of the username. By default, this is also 'postgres'.
You should also specify the password for login access.
Now our server is up and running.
There are many ways to test out this connection.
You can run a Python script and test the connection.
Being that I have a django project running on my computer, I have tested out my connection this way.
The code that I used to do this is as shown below.
When testing this out with Python or Django, just make sure that you have installed the psycopg2 and the psycopg2-binary modules.
These modules provide the software necessary to connect to and communicate with a postgresql database.
Once this was done, the connection worked, and we now
officially have a postgresql server running on our computer.
Related Resources
How to Connect a PostgreSQL Database in AWS to a Django Project
How to Connect a MariaDB Database in AWS to a Django Project
How to Connect an Oracle Database in AWS to a Django Project
How to Connect to a PostgreSQL Server from a Windows PC