Installing Apache Webserver with PHP scripting, and MySQL and PostgreSQL PHP connection driver under Ubuntu 18.04 LTS (Bionic Beaver)

1.  Download and install the Apache webserver software

# apt-get install apache2

If there are errors displayed during the installation, issue the update command before repeating the installation command.

# apt-get update --fix-missing
# apt-get install apache2


2.  Download and install the PHP scripting language

# apt-get install php

If there are errors displayed during the installation, issue the update command before repeating the installation command.

# apt-get update --fix-missing
# apt-get install php libapache2-mod-php

3.  Download and install the MySQL database server PHP connection driver.

# apt-get install php-mysql

If there are errors displayed during the installation, issue the update command before repeating the installation command.

# apt-get update --fix-missing
# apt-get install php-mysql

4.  Download and install the PostgreSQL database server PHP connection driver as follows:

# apt-get install php-pgsql

If there are errors displayed during the installation, issue the update command before repeating the installation command.

# apt-get update --fix-missing
# apt-get install php-pgsql

5.  By default, the Apache webserver should be running after installation.  You should restart the Apache webserver in order to load the PHP, MySQL and PostgreSQL support.  Issue the command:

# systemctl restart apache2.service


6. To check if the Apache process is running, issue the command

# ps ax | grep apache2

This should produce a display similar to this if it is running:

23263 ?        Ss     0:00 /usr/sbin/apache2 -k start
23265 ?        S      0:00 /usr/sbin/apache2 -k start
23266 ?        S      0:00 /usr/sbin/apache2 -k start
23267 ?        S      0:00 /usr/sbin/apache2 -k start
23268 ?        S      0:00 /usr/sbin/apache2 -k start
23269 ?        S      0:00 /usr/sbin/apache2 -k start
25621 pts/0    S+     0:00 grep --color=auto apache2

If the apache2 process is not running, the display should just produce this entry:

6433 pts/0    S+     0:00 grep --color=auto apache2

7.  To test if the Apache webserver is running using an Internet browser, type the address where the webserver is running.  On the local machine, the address is 127.0.0.1 (also known as the loopback address).  This should display a page similar to this.

This is the default page of the Apache web server.


8.  To change the default page of the Apache webserver, remove the "index.html" file in the subdirectory "/var/www/html" and create a sample HTML file called "index.html" as follows:

# cd /var/www/html
# rm index.html
# vi index.html

< html >
< head >
< title > My First HTML Page < /title >
< /head >
< body >
Hello World!< /body >
< /html >

My assumption here is, you are familiar with using the vi text editor in Linux.  You may use your own preferred text editor to create the HTML file.  After refreshing your browser, the display should look like this.

9.  To test if the Apache webserver has PHP scripting support, create the following "test.php" file and place it in the subdirectory "/var/www/html".

# cd /var/www/html
# vi test.php

< ?php 
phpinfo();
? >

Type in the address bar of the browser "http://127.0.0.1/test.php", the browser should display a page similar to this.

10.  To know if the MySQL database PHP connection driver is installed, you should see and entry in the PHP test page similar to this:

11.  Likewise, to know if the PostgreSQL database PHP connection driver is installed, you should see an entry in the PHP test page similar to this:

12.  Should there be a need to uninstall the Apache webserver, shut down the running apache2 process first before uninstalling.

# systemctl stop apache2.service
# apt-get remove apache2

PostgreSQL Replication

The following setup were tested with PostgreSQL 9.1.1.  As far as I know, the same setup can also be achieved starting from version 9.0.  Version 9 added built-in replication to the PostgreSQL database.  In the past PostgreSQL replication can be achieved by using third party software (e.g., Bucardo).  Here are the steps:

On the master server assuming an IP address of 192.168.1.107:

Step 1

Edit the postgresql.conf file as follows:

listen_addresses = 'localhost, 192.168.1.107'

wal_level=hot_standby
max_wal_senders=1
wal_keep_segments=25

Step 2

Assuming the slave server IP address of 192.168.1.104, edit the pg_hba.conf as follows:

host replication all 192.168.1.104/32 trust 

Step 3

On psql CLI (as postgres user), issue the command:

SELECT pg_start_backup('backup');

Step 4

Copy the data directory of the master server and place it on the slave server.   Use the command on psql CLI (as postgres user) to determine the physical location of the directory:

SHOW data_directory;

On the Slave Server assuming an IP address of 192.168.1.104: 

Step 5

Edit the postgresql.conf as follows:

hot_standby=on

Step 6
Create recovery.conf file as follows:

standby_mode='on'
primary_conninfo='host=192.168.1.107'

Step 7
Delete the postmaster.pid file

# rm postmaster.pid

On the Master Server:

Step 8
Issue the command on psql CLI as follows:

SELECT pg_stop_backup();

Simple ignore any warning messages.


Step 9
Restart PostgreSQL on the master server.

On the Slave Server:

Step 10
Start the PostgreSQL server.

That's it.  One last final note.  Test your setup by issuing an INSERT or CREATE TABLE command on the master server.  Check if the data are reflected on the slave server.  If it is reflected, then you have just done database replication on PostgreSQL.
 

Installing PostgreSQL as a Service

To install the PostgreSQL database as a Service under Windows, issue the command the the PostgreSQL directory as follows:

c:\pgsql\bin> pg_ctl -D c:\pgsql\data register -N PostgreSQL

In the above example, PostgreSQL is the name of the PostgreSQL service. Type the command "services.msc" to verify the name "PostgreSQL".

Initializing, Starting, Stopping, and Accessing the PostgreSQL Server on Windows

This assumes that you have the PostgreSQL binaries for Windows and that you have placed it at "c:\pgsql" directory. To set up PostgreSQL, copy the "libpq.dll" file located in the directory "c:\pgsql\lib" to the "c:\windows\system32" directory as follows:

c:\pgsql\lib> copy libpq.dll c:\windows\system32


To initialize the database, create the data directory and initialize it by issuing the "initdb" command in the "c:\pgsql\bin" directory as follows:

c:\pgsql\bin>mkdir c:\pgsql\data
c:\pgsql\bin>initdb -D "c:\pgsql\data"

To start the database, type the "pg_ctl" command as follows:

c:\pgsql\bin> pg_ctl -D "c:\pgsql\data" -l logfile start

This will start the PostgreSQL database and create the database log file "logfile".

To stop the database, type the "pg_ctl" command as follows:

c:\pgsql\bin> pg_ctl -D "c:\pgsql\data" stop

To access the database at the DOS command prompt on Windows, type the command:

c:\pgsql\bin> cmd.exe /c chcp 1252
c:\pgsql\bin> psql

Compiling PostgreSQL on Windows

The following compilation procedure has been tested on MS Windows XP Professional using PostgreSQL 8.4.0, MinGW 5.1.4 and MSYS 1.0.11.rc-1.

Steps

1. Download MinGW and MSYS from http://www.mingw.org
2. Download PostgreSQL source code from http://www.postgresql.org
3. Download the zlib compression library from http://www.zlib.org
4. Install MinGW under the directory c:\mingw
5. Install MSYS under the directory c:\msys. Indicate where mingw is located when prompted by MSYS.

6. Add the path to Windows for the following directories:

c:\msys\1.0\bin
c:\msys\1.0\mingw\bin
c:\msys\1.0\mingw\sbin
c:\mingw\bin


This is done by going to "Start->Settings->Control Panel->System icon->Advanced tab->Environment Variables button". Then under the "System Variables" grouping, Select the "Path" and choose "Edit" button. Add the above directories at the end by delimiting it with a semi-colon (;).

7. Install the zlib library:

a) Unpack the zlib in MSYS (c:\msys\home\{username}\zlib)
b) Run MSYS by typing the msys.bat file in c:\msys\1.0
c) At the MSYS terminal, type the following:

$ cd /home/{username}/zlib
$ make -f win32/Makefile.gcc

d) Copy the zlib1.dll to c:\mingw\bin
e) Copy the libzdll.a and libz.a to c:\mingw\lib
f) Copy the zlib.h and zconf.h to c:\mingw\include


8. Unpack the PostgreSQL source code and copy it to the directory c:\msys\1.0\home\{username}\postgresql.

The source code is in tar.gz format. You can use the 7zip software from http://www.7-zip.org to unpack the source code.

9. At the MSYS terminal, go to the PostgreSQL source code directory and run the compile commands as follows:

$ cd /home/{username}/postgresql
$ ./configure
$ make
$ make install

This will create the PostgreSQL binaries in the following directory c:\msys\1.0\local\pgsql

10. Create the default data directory for PostgreSQL as "c:\msys\1.0\local\pgsql\data"

11. You can now copy the folder of PostgreSQL binaries to your desired location like "c:\pgsql" or create an installer package for your other machines.

The MinGW and MSYS software are not needed by PostgreSQL once you are able to create its binaries under Windows.

Web-based Administration of PostgreSQL

The web-based administration tool for PostgreSQL is the "phppgadmin" software. The software is freely downloadable at the "http://phppgadmin.sourceforge.net" website.

To install the phppgadmin on Ubuntu 9.04, issue the command:

# apt-get install phppgadmin

Afterwards, the phppgadmin is now accessible using the browser at the localhost address of "http://localhost/phppgadmin".


Common Problem

1. You cannot access the database using the "postgres" user.

Solution:

login as postgres user and create another administrator user as follows:

# su postgres
$ psql
$ createuser myadmin

When prompted if the user will be "administrator", choose "yes" as follows:

postgres@asus:/home/clemrasul$ createuser
Enter name of role to add: myadmin
Shall the new role be a superuser? (y/n) y

Then, use the new administrator username and password to login into the phppgadmin web interface.

GUI Administration of PostgreSQL

The graphical user interface (GUI) administration tool for PostgreSQL is "pgadmin". To install "pgadmin" on Ubuntu 9.04, issue the command:

#apt-get install pgadmin3

This will install the "pgadmin" software under "Applications -> Programming -> PgAdmin III".

Accessing PostgreSQL on CLI

The command line interface (CLI) of PostgreSQL is called "psql". To access the PostgreSQL server, issue the following commands:

# su postgres
$ psql

The first command is to shift from the "root" user to the "postgres" user. PostgreSQL does not allow "root" login. Instead, it uses the "postgres" user as the default administrator of the system.

The second command is to invoke the "psql" CLI under the user "postgres".

The following is the screen example of the above-mentioned command.

root@asus:/etc/init.d# su postgres
postgres@asus:/etc/init.d$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#


Given the psql, you can now create databases and users.

Installing PostgreSQL on Ubuntu

What is PostgreSQL?

PostgreSQL is a production-grade multi-platform SQL database system. The database system is ANSI SQL92/99 compliant. The software is freely downloadable at http://www.postgresql.org website and is licensed under the BSD open source license.


Installation

To install PosgreSQL on Ubuntu 9.04, issue the command:

# apt-get install postgresql


To start PostgreSQL, issue the command:

#/etc/init.d/postgresql-8.3 start

To restart PostgreSQL, issue the command:

#/etc/init.d/postgresql-8.3 restart

To stop PostgreSQL, issue the command:

#/etc/init.d/postgresql-8.3 stop


More Notes

a) Default port of PostgreSQL is "5342"
b) Default database administrator username is "postgres"
c) Default database template is "template1"


Limits

According to the PostgreSQL website, the following are PostgreSQL 8.3 limits:

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

License Text

License

PostgreSQL is released under the BSD license.

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2008, The PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Solution to PostgreSQL Serial Limitations

In PostgreSQL, an automatic incrementing column is called "serial". This is often used as a primary key of a table. The default serial column is of type INT4 which has a maximum value of 2,147,483,647. To increase this maximum number, create an INT8 data type for your serial column, as follows:

>CREATE SEQUENCE person_id_seq;
>CREATE TABLE person( id INT8 NOT NULL DEFAULT nextval('person_id_seq'), name TEXT);
>CREATE UNIQUE INDEX person_id_key on person(id);

This will increase the capacity of the column to 9,223,372,036,854,775,807. I don't know if there is an INT16 column type. If there is, theoretically we can increase further the capacity of the column by a factor of 2 since PostgreSQL does not support unsigned integers as far as I know.