How to Install PostgreSQL and pgAdmin on FreeBSD 11
PostgreSQL or in short Postgres, is an open source Object-Relational Database Management System (ORDBMS). It is in active development since 1996 and evolved to be one of the best ORDMS. It supports all the features of RDBMS along with support for object-oriented database models. Objects, classes and inheritance are directly supported in database schemas and in the query language. PostgreSQL can be used to host the database for a variety of applications ranging from small websites to large cloud based applications or data warehousing.
In this tutorial, we will learn how to install PostgreSQL and pgAdmin on a FreeBSD 11 server.
Prerequisite
- Minimal FreeBSD 11 server. 8 GB RAM recommended.
- Sudo user with root privileges.
Installing PostgreSQL
At first update the server’s repository information.
sudo pkg update
pkg is the built in package manager for FreeBSD. Next, download and install PostgreSQL server and client packages.
sudo pkg install postgresql96-server postgresql96-client
Press y to proceed with the installation. pkg will now find and install the specified packages along with the required dependencies.
Once the installation finishes, enable PostgreSQL to start automatically at boot time.
sudo sysrc postgresql_enable=yes
Alternatively, to start PostgreSQL at boot time, you can also add postgresql_enable="YES" at the end of the /etc/rc.conf using any of your favorite text editor.
Now initialize the database using the following command.
sudo service postgresql initdb
initdb creates a new postgresql database cluster which is a collection of databases that are managed by a single server instance.
You can now start the PostgreSQL database server using the following command.
sudo service postgresql start
PostgreSQL database server is now installed.
Enable Remote Connections and Password Authentications
If the database server is to be accessed remotely, you will need to make a few changes in PostgreSQL configuration. In this tutori, l we will be using nano editor. If you do not have nano installed, you can run:
sudo pkg install nano
Open the configuration file using nano editor.
sudo nano /var/db/postgres/data96/postgresql.conf
Scroll down to find the lines shown below. Uncomment listen_addresses and change its value from localhost to *. You can also change the default port from 5432 to any other value you want.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/tmp' # comma-separated list of directories
By default PostgreSQL allows everyone to connect to psql shell without authenticating the user who are already logged in as PostgreSQL user. When the database is publicly accessible, you will need to change the authentication method to something more secure. You can use md5 authentication method which uses encrypted password for authentication. Further, you can also specify the IP addresses which are allowed for authentication.
Open the pg_hba.conf file using your favorite text editor.
sudo nano /var/db/postgres/data96/pg_hba.conf
Change the method of authentication from trust to md5 for host type connection. Further, add the address of the client machine from which database is to be accessed. To allow all clients to use md5 based authentication, use 0.0.0.0/0 as address.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 md5
For above changes to take effect, you will need to restart the PostgreSQL server.
sudo service postgresql restart
Changing Password and Creating User
By default, PostgreSQL creates a user postgres and group postgres to maintain PostgreSQL instance. To set the password for postgres user, run the following command.
sudo passwd postgres
After changing the password, login as postgres user.
su - postgres
You can now enter the PostgreSQL shell using the following command.
psql
You will see that the shell has changed to PostgreSQL shell.
psql (9.6.3) Type "help" for help. postgres=#
You can now run SQL queries from this shell. To exit from the PostgreSQL shell, type \q or \quit.
You can create a new role interactively by typing:
createuser --interactive
The script will ask you to enter the username and whether the user is a superuser.
$ createuser --interactive Enter name of role to add: new_user Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y
Alternatively, you can run:
createuser -sdrP new_user
The meaning of the parameters are as follows.
- s: This user will be superuser.
- d: This user can create new databases.
- r: This user can create new roles.
- P: Assign a password to this role.
The above command will ask for the password for the new_user also.
To create a new database, you can run the following command.
createdb new_db
Now switch to PostgreSQL shell using psql command. If there is no password for the user, provide the password of the newly created account:
ALTER USER new_user WITH ENCRYPTED PASSWORD 'password';
Now grant all privileges of the database to the newly created user by running the query:
GRANT ALL PRIVILEGES ON DATABASE new_db TO new_user;
Exit the editor by running \q.
Installing pgAdmin
pgAdmin is the most popular open source application for managing a PostgreSQL database server. pgAdmin provides a feature rich graphical user interface to easily manage the databases. It is written in Python and Javascript/jQuery. It is available in multiple environments like Linux, Windows, Unix and available in desktop and server mode.
In this tutorial, we will install pgAdmin in server mode on the same server on which we have installed PostgreSQl.
pip is a package manager for Python packages. It is used to install and manage Python packages and dependencies. If you are logged in as postgres user, you will need to switch to the normal user with sudo privileges.
su - sudo_user
To install pip on your system, run:
sudo python -m ensurepip
It is recommended to use virtualenv to create isolated Python environment. Virtualenv creates an environment with its own Python installation which does not support libraries with global or another virtual environment. Run the following command for installation of Virtualenv.
sudo pkg install py27-virtualenv
Now create a virtual environment for pgAdmin by running:
virtualenv pgadmin4
Activate the Virtual environment by typing:
. pgadmin4/bin/activate
You will see that shell has changed to (pgadmin4) $.
Install few Python dependencies by typing:
sudo pip install cryptography pyopenssl ndg-httpsclient pyasn1
cryptography package provides cryptographic tools, pyopenssl is the Python wrapper module for OpenSSL library. ndg-httpsclient used pyopenssl to provide enhanced HTTPS support and pyasn1 is python implementation of ASN.1. These packages are required for pgAdmin to work.
pgAdmin4 also requires SQLite package. SQLite is also a SQL supported database management system. Instead of requiring a separate server, SQLite stores the data in flat files on disk. Install Python SQLite3 package by typing:
sudo pkg install py27-sqlite3
Download the Python wheel package for pgAdmin by typing:
wget --no-check-certificate https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl
You can always check for the link to the latest version of the application on pgAdmin download page.
Now install the package by running:
pip install pgadmin4-1.5-py2.py3-none-any.whl
You can now run the application using:
python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
On first run the above command will ask for your email address and password to create initial pgAdmin user.
(pgadmin4) $ python ./pgadmin4/lib/python2.7/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: your_email Password: your_new_password Retype password: Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.
The above command will start the server on port 5050 will listen to localhost only. To access the server from outside the network, you will need to make some configuration change. Copy the pgAdmin configuration file.
sudo cp ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config.py ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py
Now edit the local copy of configuration file using your favorite editor.
sudo nano ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py
Change the default server value from localhost to 0.0.0.0, if you want you can also change the port on which the application is listening.
DEFAULT_SERVER = '0.0.0.0' # The default port on which the app server will listen if not set in the # environment by the runtime DEFAULT_SERVER_PORT = 5050
Start the server again by typing:
python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py
You will now be able to access the pgAdmin interface on http://your_server_IP:5050.
Conclusion
You should now have PostgreSQL and pgAdmin installed in your server. You can now use pgAdmin to manage the databases on the PostgreSQL server.
