Install and Configuration pgAdmin for PostgreSQL on FreeBSD

pgAdmin is a management tool for PostgreSQL and derivative relational databases such as EDB (Advanced Server EnterpriseDB). This program can be run as a web or desktop application. pgAdmin is a free and very popular open source platform. pgAdmin was created to make things easier for PostgreSQL database users, because this program has a graphical user interface for managing relational databases. Some features include query tools for SQL statements and importing/exporting csv files.

Graphical user interface (GUI) tools have become a standard part of today's technology workflow. For PostgreSQL management, pgAdmin is considered the right solution for users who need a display in the form of an image or GUI, just like using Windows. As the developer says, pgAdmin 4 is the latest version, and its development involved a complete rewrite of the original pgAdmin tools.

pgAdmin is one of the leaders for open source management based on PostgreSQL, which is often referred to as the most advanced database platform in the world. pgAdmin 4 is designed to meet the needs of both novice and experienced Postgres users, providing a powerful graphical interface that simplifies the creation, maintenance, and use of database objects.

pgAdmin is a free software project released under the PostgreSQL/Artistic license. pgAdmin 4 is software built using Python and JavaScript/jQuery. Desktop runtimes written in C++ with Qt allow them to run standalone for individual users, or web application code can be deployed directly on a web server for use by one or more users through their web browsers.

Another feature that is no less interesting than pgAdmin is that it can handle SQL queries, maintenance, and other necessary processes without using the command prompt. Apart from that, pgAdmin also provides monitoring tools that allow users to see the status of operations at a glance, and help automate work on a routine and periodic basis. Overall, it's safe to say pgAdmin is a valuable addition to the workflow of most PostgreSQL users.

With so much documentation, it's not difficult to get started, even if you've never installed a GUI tool on top of a database before. The next step is to ensure pgAdmin is compatible with the PostgreSQL installation that has been installed on your server.

In this article, we will learn about PGAdmin, a PostgreSQL management tool. As you know, SQL Server Management Studio (SSMS) and MySQL Workbench are GUI management tools for SQL Server and MySQL respectively. Likewise, to manage the Postgres database and its services, PGAadmin is used. PGAdmin is a web-based GUI tool used to interact with Postgres database sessions, both locally and on remote servers. You can use PGAdmin to perform any kind of database administration required for a Postgres database.

This article was written based on practices carried out on the FreeBSD 13.2 server. The following are the system specifications used for writing this article.


1. System specifications

OS: FreeBSD 13.2
IP Addres: 192.168.5.2
Domain: unixexplore.com
Versi PostgreSQL: postgresql15
Versi python: python39
Versi pgadmin: pgadmin3
Versi pip: pip23.1





2. pgadmin installation

In this article we will not discuss how to install PostgreSQL, we will assume that PostgreSQL has been installed on the FreeBSD server. So we continue with the pgadmin installation. On FreeBSD systems, it is best to use the ports system to install pgadmin. Here's how to install pgadmin with the FreeBSD ports system.

root@ns1:~ # cd /usr/ports/databases/py-sqlite3
root@ns1:/usr/ports/databases/py-sqlite3 #
make install clean
root@ns1:/usr/ports/databases/py-sqlite3 #
cd /usr/ports/lang/python39
root@ns1:/usr/ports/lang/python38 #
make install clean
root@ns1:/usr/ports/lang/python38 #
cd /usr/ports/devel/py-pip
root@ns1:/usr/ports/devel/py-pip #
make install clean
root@ns1:/usr/ports/devel/py-pip #
cd /usr/ports/devel/py-virtualenv
root@ns1:/usr/ports/devel/py-virtualenv # make install clean

The command above is a script to install the dependencies required by the pgadmin program. The command above is very useful for running pgadmin on a FreeBSD system. After all pgadmin dependencies have been installed, below is how to install pgadmin.

root@ns1:~ # cd /usr/ports/databases/pgadmin3
root@ns1:/usr/ports/databases/pgadmin3 #
make install clean


3. Create a Python39 Symlink

Because pgadmin runs with the python application and many versions of python are installed automatically on FreeBSD, creating python symlinks is very necessary and important. This symlink can be used to confirm the version of python being used, because in this article using python38 the symlink must be python38.

Here are the steps to create a pyhton38 symlin on FreeBSD. The first step is to delete the existing symlink file.

root@ns1:~ # rm -R -f /usr/local/bin/python
root@ns1:~ #
ln -s /usr/local/bin/python3.9 /usr/local/bin/python

The first script above explains deleting the python binary file and the second script creates a python38 symlink. The two script commands above can also be used to resolve errors in Python such as "env python: No such file or directory". After the symlink file is created, restart the computer so that the symlink is active.

root@ns1:~ # reboot


4. Run pgadmin

To run pgadmin, we first have to create a working folder. We will create a working project with the name "projectPgadmin" and we will place it in the /tmp folder.

root@ns1:~ # mkdir -p /tmp/projectPgadmin
root@ns1:~ #
cd /tmp/projectPgadmin

After that, continue by creating a working project in the Python virtual environment with the name "pgadmin".

root@ns1:/tmp/projectPgadmin # python -m venv pgadmin
root@ns1:/tmp/projectPgadmin #
cd pgadmin

The above script is used to create a python working project with the name "pgadmin" and the working project is in the python virtual environment. The work project is not yet active, now we will activate the work project, type the script below to activate the work project "pgadmin". For more details about working in the python virtual environment, you can read the article above.

root@ns1:/tmp/projectPgadmin/pgadmin # source bin/activate.csh
(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin #

Then continue with the script below to update the pip application.

(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin # pip install --upgrade pip

After pip has been successfully updated, type the following command to install the pgadmin repository.

(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin # python3 -m pip install cryptography==3.3 pyopenssl ndg-httpsclient pyasn1 simple-websocket
(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin # python3 -m pip install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v6.21/pip/pgadmin4-6.21-py3-none-any.whl

The next step is to type the following command to copy the config.py file to the config_local.py file.

(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin # cd ..
(pgadmin) root@ns1:/tmp/projectPgadmin # cp ./pgadmin/lib/python3.9/site-packages/pgadmin4/config.py ./pgadmin/lib/python3.9/site-packages/pgadmin4/config_local.py

When the file copy process is complete, edit the config_local.py file and change the Server IP to the Private IP of our FreeBSD server computer. If you edit the config_local.py file using the "ee" or "vim" editor, you must first exit the Python virtual environment, use the "deactivate" command to exit the Python virtual environment.

(pgadmin) root@ns1:/tmp/projectPgadmin # deactivate
root@ns1:/tmp/projectPgadmin #
ee ./pgadmin/lib/python3.9/site-packages/pgadmin4/config_local.py
DEFAULT_SERVER = '192.168.5.2'
DEFAULT_SERVER_PORT = 5050


5. Test pgadmin

After all the applications are configured, now we will test whether the pgadmin application can run on the FreeBSD server. To carry out this test, we must be active in the Python virtual environment. The following is the script used to activate pgadmin, starting from the /root folder.

root@ns1:~ # cd /tmp/projectPgadmin/pgadmin
root@ns1:/tmp/projectPgadmin/pgadmin #
source bin/activate.csh
(pgadmin) root@ns1:/tmp/projectPgadmin/pgadmin # cd ..
(pgadmin) root@ns1:/tmp/projectPgadmin #
python ./pgadmin/lib/python3.9/site-packages/pgadmin4/pgAdmin4.py

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address:
datainchi@gmail.com
Password:
gunungrinjani
Retype password:
gunungrinjani
pgAdmin 4 - Application Initialisation
======================================

Starting pgAdmin 4. Please navigate to http://192.168.5.2:5050 in your browser.
* Serving Flask app 'pgadmin' (lazy loading)
* Environment: production
WARNING: This is a development server. Do not use it in a production deployment.
Use a production WSGI server instead.
* Debug mode: off

The green script above is to activate the pgadmin application. Fill in the email according to the existing email and the password according to your wishes. In this case, my admin gave me the password "gunungrinjani".

Once you are sure that the pgadmin application is active, open the Google Chrome, Yandex or Firefox web browser, in the address bar type "http://192.168.5.2:5050".

With pgadmin, you can easily use the PostgreSQL database, because the display is in graphical or GUI form, making it easier for users to manage, modify and run PostgreSQL.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post