How Do You Write a Backend PHP Script to Query Postgres and Create a Database Name “foobar”?

Problem scenario
You are running Ubuntu Linux, and you want to create a .php script to be ran at the command prompt like this:

php good.php

You want this to open a PosgreSQL connection and issue a DDL command to create a database.  This is not a PHP web page.

This solution is only slightly more complicated than using PHP to run a basic SQL query in a Postgres database.

1.  Go to the command prompt of PSQL.  These commands may help you:

sudo -i -u postgres

2.  Create a role from the psql command prompt.  Here is an example:

create role orangeuser;

3.  Alter the role to have the login trait and superuser attribute.

alter role orangeuser login superuser;

4.  Install php5-pgsql or php7.0-pgsql.  It depends on your version of PHP.  Use this command to find out what version of PHP you have:  php --version

To install the php pgsql package on Ubuntu, you should run a command like one of these as root:  
apt-get install php5-pgsql
apt-get install php7.0-pgsql

5.  Create a .php file (e.g., cool.php) with the following as the content:

/* This assumes your server with PHP has access to the server with Postgres.  The IP address in the script is the local loopback.  You may have to change the IP address for this to work.  Naturally you will have to change the database name, the user and password in the PHP script to the credentials for your Postgres instance. */

   $host        = "host=";
   $port        = "port=5432";
   $dbname      = "dbname=contint";
   $credentials = "user=jdoe password=softpassword";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   pg_query("CREATE DATABASE foobar");


 6.  Run it like this:  php cool.php

If you want to write a front-end web page to execute a SQL command, see this posting:
How Can You Accept User Input with PHP and Use It in a SQL Command?

Leave a comment

Your email address will not be published. Required fields are marked *