Ubuntu 12.04 Postgres Access for C

Ubuntu 12.04 Postgres Access for C

Description

This post describes the steps to install the software needed:

  • to be able to access a PostgreSQL database table from C,
  • starting from a DigitalOcean Ubuntu 12.04 Droplet, Virgin Image.

Step 1 Create DigitalOcean Ubuntu 12.04 Droplet

  • Log into DigitalOcean (DO) Droplets
  • Click the “Create Droplet” Button at the top right.
  • Name your Droplet Hostname. Select Size. Select Region.
  • Select Image, On “Distributions Tab”/UbuntuTile_bottom_half, click & select “12.04.05 x32”
  • Click Big Green Button at bottom “Create Droplet”
  • Copy the Droplet ip address for later access.

Step 2 Install PostgreSQL on Ubuntu 12.04

I follow “How To Install and Use PostgreSQL on Ubuntu 12.04”.

root@whatever:~# # logged in as root
root@whatever:~#
root@whatever:~# apt-get update

[**DELETED LINES**]

Reading package lists... Done

root@whatever:~#
root@whatever:~# # download Postgres and its helpful accompanying dependencies
root@whatever:~# sudo apt-get install postgresql postgresql-contrib

Reading package lists... 0%

[**DELETED LINES**]

Moving configuration file /var/lib/postgresql/9.1/main/pg_ident.conf to /etc/postgresql/9.1/main...
Configuring postgresql.conf to use port 5432...
update-alternatives: using /usr/share/postgresql/9.1/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.
* Starting PostgreSQL 9.1 database server
[ OK ]
Setting up postgresql (9.1+129ubuntu1) ...
Setting up postgresql-contrib-9.1 (9.1.15-0ubuntu0.12.04) ...
Setting up postgresql-contrib (9.1+129ubuntu1) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place

Step 3 Add a new [Linux] User

The "How To Install and Use PostgreSQL on Ubuntu 12.04" article says the next step is to "Create Your PostgreSQL Roles and Databases".

However, first, before that, I will add a new [non root, Linux] User, since this is a virgin image and I don't want to be messing around in root all the time.

I will however, give the new user Admin Privileges.

root@whatever:~# # Joe's first step is to add a new [Linux] user.
root@whatever:~# #     now following https://www.digitalocean.com/community/tutorials/how-to-add-delete-and-grant-sudo- privileges-to-users-on-a-debian-vps
root@whatever:~# #         assume you are currently logged in as the root user, pwd == ~
root@whatever:~#
root@whatever:~# adduser joed   # or whatever username you want

[**DELETED LINES** you get asked a lot of questions in here]

Is the information correct? [Y/n]
root@whatever:~# su - joed   # www.linfo.org/su.html says "The su (short for substitute user)..."
joed@whatever:~$ pwd
/home/joed
joed@whatever:~$ exit  # exit from joed goes back to root
logout
root@whatever:~# pwd
/root

Step 4 Grant [Linux] Users Administrative Privileges

I want the newly added Linux user, "joed", to have Administrative Privileges.

root@whatever:~#
root@whatever:~# # Grant Users Administrative Privileges
root@whatever:~# #    enable sudo privileges
root@whatever:~# #        run the   visudo   command
root@whatever:~# #            i set visudo command editor to be nano  - i hate vi
root@whatever:~# # see http://superuser.com/a/821888/236556
root@whatever:~# # using WinSCP & np++ i edited /etc/sudoers  DIRECTLY, adding the following lines under the Defaults section at the beginning
root@whatever:~# #    Defaults        editor="/usr/bin/nano"
root@whatever:~#
root@whatever:~#
root@whatever:~# # now using visudo [with nano] i add the following line
root@whatever:~# #    joed    ALL=(ALL:ALL) ALL
root@whatever:~# #    under these 2 lines
root@whatever:~# #    # User privilege specification
root@whatever:~# #    root    ALL=(ALL:ALL) ALL
root@whatever:~#
root@whatever:~# #    N.B. i used the visudo command instead of editing it directly with np++
root@whatever:~# #        because i read SOMEPLACE?? that visudo checks the syntax of the lines for correctness
root@whatever:~#
root@whatever:~#
root@whatever:~#
root@whatever:~# # now let's check to see if joed has sudo priveleges
root@whatever:~#
root@whatever:~# su joed
joed@whatever:/root$
joed@whatever:/root$
joed@whatever:/root$ # check if i have sudo priveleges
joed@whatever:/root$     # see http://superuser.com/a/553939/236556
joed@whatever:/root$
joed@whatever:/root$ sudo -v
[sudo] password for joed:
joed@whatever:/root$ sudo -l
Matching Defaults entries for joed on this host:
env_reset, secure_path=/usr/local/sbin\:/usr/local/bin\:/usr/sbin\:/usr/bin\:/sbin\:/bin, editor=/usr/bin/nano

User joed may run the following commands on this host:
(ALL : ALL) ALL
joed@whatever:/root$ exit
exit
root@whatever:~#
root@whatever:~#
root@whatever:~# # OK joed is a new Linux user  &  has sudo priveleges.
root@whatever:~#

Step 5 Make the new Linux User a PostgreSQL "superuser".

Now we "pop the stack" back into "How To Install and Use PostgreSQL on Ubuntu 12.04" at the "Create Your PostgreSQL Roles and Databases" step.

Remember, in the big picture, we are making the Linux user, joed, become a PostgreSQL superuser & accessing a PostgreSQL table from the C language.

root@whatever:~#
root@whatever:~#
root@whatever:~# # Create Your PostgreSQL Roles and Databases
root@whatever:~# #    To begin creating custom users, first switch into the [PostgreSQL] default [Linux] user [== "postgres"]
root@whatever:~# sudo su - postgres      # i BELIEVE i don't need sudo because i am doing this as root but WHY NOT FOLLOW THE DIRECTIONS  🙂
postgres@whatever:/root$
postgres@whatever:/root$ cd ~
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ # OK so we are in the Linux user account named postgres, added by the PostgreSQL install
postgres@whatever:~$ #     now we will do a PostgreSQL createuser command  to make the existing Linux user, joed, be a PostgreSQL superuser
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ pwd
/var/lib/postgresql
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ createuser --pwprompt
Enter name of role to add: joed
Enter password for new role:  <enter the pw>
Enter it again: <enter the pw>
Shall the new role be a superuser? (y/n) y
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ # now let's look for joed in the pg database
postgres@whatever:~$
postgres@whatever:~$ psql
psql (9.1.15)
Type "help" for help.

postgres=# \dt
No relations found.
postgres=# \l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \du-- show users
postgres=# \du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
joed      | Superuser, Create role, Create DB, Replication | {}
postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# -- N.B. -- is the psql  COMMENT indicator
postgres=# -- list databases & tables for current db  http://dba.stackexchange.com/a/1288
postgres=# --     also above tells how to switch the currnent db
postgres=# -- OK logout of the psql interpreter (\q) & exit back to root Linux user & log back in as postgres_user joed Linux
postgres=# \q
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ exit
exit
root@whatever:~#
root@whatever:~# # OK joed is a PostgreSQL superuser

Step 6 Create a PostgreSQL Database

I am going to create a PostgreSQL database named "omnia". I will do this as the PostgreSQL user named "postgres".

N.B We are back into "How To Install and Use PostgreSQL on Ubuntu 12.04" at the "Create Your PostgreSQL Roles and [now] Databases" step.

root@whatever:~#
root@whatever:~# # ok [from root] log back in as Linux user == postgres
root@whatever:~# su postgres
postgres@whatever:/root$ cd ~
postgres@whatever:~$ # re-follow https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-12-04
postgres@whatever:~$ # create your first usable postgres database
postgres@whatever:~$ #    i create a named "omnia"
postgres@whatever:~$ createdb omnia
postgres@whatever:~$
postgres@whatever:~$
postgres@whatever:~$ psql
psql (9.1.15)
Type "help" for help.

postgres=# \l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
omnia     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# -- -- http://stackoverflow.com/questions/3949876/how-to-switch-databases-in-psql
postgres=# \connect omnia
You are now connected to database "omnia" as user "postgres".
omnia=# \q
postgres@whatever:/root$ # let's try this as joed
postgres@whatever:/root$ exit
exit
root@whatever:~# su joed
joed@whatever:/root$ cd ~
joed@whatever:~$ pwd
/home/joed
joed@whatever:~$ # follow https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-12-04
joed@whatever:~$     # log into the correct database==omnia(using the psql -d omnia)
joed@whatever:~$ psql -d omnia
psql (9.1.15)
Type "help" for help.

omnia=# -- YAHOO we're in as joed a postgres superuser
omnia=# \l
List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
omnia     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|          |          |             |             | postgres=CTc/postgres
(4 rows)

omnia=# \du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
joed      | Superuser, Create role, Create DB, Replication | {}
postgres  | Superuser, Create role, Create DB, Replication | {}

omnia=# \q
joed@whatever:~$
joed@whatever:~$ pwd
/home/joed

Step 7 Create & Initialize a Table

I will create the table named "test.

I will initialize (load it up) from a .csv file.

I will do this as the [Linux & PostgreSQL] user named "joed".

[joed@whatever trial_test_matrix]$ # now let's make the table named "test" in the "omnia" db
[joed@whatever trial_test_matrix]$ psql -d omnia
psql (9.1.15)
Type "help" for help.

omnia=# CREATE TABLE test(
omnia(#     id INTEGER PRIMARY KEY,
omnia(#     test_ix INTEGER,
omnia(#     origin CHAR(512),
omnia(#     max_prime INTEGER,
omnia(#     primes_tail_0 INTEGER,
omnia(#     primes_tail_1 INTEGER,
omnia(#     primes_tail_2 INTEGER,
omnia(#     primes_tail_3 INTEGER,
omnia(#     primes_tail_4 INTEGER,
omnia(#     primes_tail_5 INTEGER,
omnia(#     primes_tail_6 INTEGER,
omnia(#     primes_tail_7 INTEGER
omnia(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
omnia=# \dt
List of relations
Schema | Name | Type  | Owner
--------+------+-------+-------
public | test | table | joed
(1 row)

omnia=# -- show the column names for test
omnia=# \d test

Table "public.test"
Column     |      Type      | Modifiers
---------------+----------------+-----------
id            | integer        | not null
test_ix       | integer        |
origin        | character(512) |
max_prime     | integer        |
primes_tail_0 | integer        |
primes_tail_1 | integer        |
primes_tail_2 | integer        |
primes_tail_3 | integer        |
primes_tail_4 | integer        |
primes_tail_5 | integer        |
primes_tail_6 | integer        |
primes_tail_7 | integer        |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

omnia=# \q
[joed@whatever trial_test_matrix]$ # now load up the omnia.test table from init.dbcsv []n]o] ]h]e]a]d]e]r]s]
[joed@whatever trial_test_matrix]$
[joed@whatever trial_test_matrix]$ psql -d omnia
psql (9.1.15)
Type "help" for help.

omnia=# -- now import from csv
omnia=# --    see http://stackoverflow.com/a/2987451/601770
omnia=# --    we will do something like COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;
omnia=# --    COPY zip_codes FROM '/path/to/csv/ZIP_CODES.txt' DELIMITER ',' CSV;test/home/joed/expr/trial_test_matrix/init.csv
omnia=# -- load up the table from init.csv file NO HEADER, 1000 records
omnia=# COPY test FROM '<path to>/init.csv' DELIMITER ',' CSV;
COPY 1000
omnia=# -- yahoo
omnia=#
omnia=# SELECT test_ix, max_prime FROM test WHERE id<20;

test_ix | max_prime
---------+-----------
0 |       840
1 |       980
2 |       900
3 |       900
4 |       830
5 |       950
6 |       990
7 |       800
8 |       960
9 |       820
10 |       910
11 |       930
12 |       920
13 |       950
14 |       960
15 |       820
16 |       830
17 |       880
18 |       830
(19 rows)

omnia=# \q

[joed@whatever trial_test_matrix]$ # OK we have created a table named test
[joed@whatever trial_test_matrix]$ #      AND loaded it up with data from a .csv file

Step 8 Install gcc (the C compiller)

Aparently, gcc does not come with the standard DO Ubuntu image. Who'd 'a thunk it? 🙂

I'll install it as root.

[joed@whatever SQL]$
[joed@whatever SQL]$ # The tale of "woe" begins here. 🙂
[joed@whatever SQL]$
[joed@whatever SQL]$ # Try to compile my C program named tse_analogous_db.c
[joed@whatever SQL]$ #    located in the   .../SQL dir
[joed@whatever SQL]$
[joed@whatever SQL]$ gcc -c -I/usr/local/pgsql/include  tse_analogous_db.c
The program 'gcc' can be found in the following packages:
* gcc
* pentium-builder
Ask your administrator to install one of them
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # well that does that   -- i have to install gcc
[joed@whatever SQL]$ # see http://askubuntu.com/a/271561/144883
[joed@whatever SQL]$ #     via the toolchain PPA
[joed@whatever SQL]$ #          add the PPA to your system
[joed@whatever SQL]$ # i am going to do this as root
[joed@whatever SQL]$
[joed@whatever SQL]$ exit
root@whatever:~#
root@whatever:~# # OK now let's install gcc according to  http://askubuntu.com/a/271561/144883  - REALLY
root@whatever:~# #    via the toolchain PPA
root@whatever:~# #          add the PPA to your system
root@whatever:~#
root@whatever:~# # First command listed by the link i am following:
root@whatever:~# #    http://askubuntu.com/a/271561/144883
root@whatever:~# #        apt-get install python-software-properties
root@whatever:~#
root@whatever:~# apt-get install python-software-properties

Reading package lists... 0%
[**DELETED LINES**]
After this operation, 651 kB of additional disk space will be used.
Do you want to continue [Y/n]? y
[**DELETED LINES**]
Setting up python-software-properties (0.82.7.7) ...
root@whatever:~#
root@whatever:~#
root@whatever:~# # Next command listed by the link i am following:
root@whatever:~# #    http://askubuntu.com/a/271561/144883
root@whatever:~# #        add-apt-repository ppa:ubuntu-toolchain-r/test
root@whatever:~#
root@whatever:~#
root@whatever:~# add-apt-repository ppa:ubuntu-toolchain-r/test
You are about to add the following PPA to your system:
Toolchain test builds; see https://wiki.ubuntu.com/ToolChain

More info: https://launchpad.net/~ubuntu-toolchain-r/+archive/ubuntu/test
Press [ENTER] to continue or ctrl-c to cancel adding it

gpg: keyring `/tmp/tmpNRzYg4/secring.gpg' created
[**DELETED LINES**]
gpg:               imported: 1  (RSA: 1)
OK
root@whatever:~#
root@whatever:~#
root@whatever:~#
root@whatever:~# # Next command listed by the link i am following:
root@whatever:~# #    http://askubuntu.com/a/271561/144883
root@whatever:~# #        apt-get update
root@whatever:~#
root@whatever:~# apt-get update


0% [Working]
[**DELETED LINES**]
Reading package lists... Done

root@whatever:~#
root@whatever:~#
root@whatever:~#
root@whatever:~#
root@whatever:~# # Next command listed by the link i am following:
root@whatever:~# #    http://askubuntu.com/a/271561/144883
root@whatever:~# #        apt-get install gcc-4.8
root@whatever:~#
root@whatever:~# #    The above link ALSO says
root@whatever:~# #        The latest available version of gcc, for 12.04, is 4.8.1 and is available in the toolchain PPA
root@whatever:~#
root@whatever:~# apt-get install gcc-4.8.1

Reading package lists... 0%
[**DELETED LINES**]
After this operation, 70.6 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
[**DELETED LINES**]
Setting up manpages-dev (3.35-0.1ubuntu1) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
root@whatever:~#
root@whatever:~#
root@whatever:~#
root@whatever:~# # Next is the FINAL command listed by the link i am following:
root@whatever:~# #    http://askubuntu.com/a/271561/144883
root@whatever:~# #        update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-4.8 50
root@whatever:~#
root@whatever:~# update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-4.8 50
root@whatever:~#
root@whatever:~#
root@whatever:~# # OK gcc is now installed 🙂
root@whatever:~#

Step 9 Install libpq-dev to access PostgreSQL from C

The libpq-dev code library for accessing PostgreSQL from C needs to be installed.

I'll install it as root.

root@whatever:~#
root@whatever:~# su joed
joed@whatever:/root$ # shorten the prompt
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # install libpq-dev
[joed@whatever SQL]$ # Following http://askubuntu.com/a/182062/144883
[joed@whatever SQL]$ # Do this as root
[joed@whatever SQL]$ exit
exit
[root@whatever ~]#
[root@whatever ~]# apt-get install libpq-dev

Reading package lists... 0%
[**DELETED LINES**]
After this operation, 9,328 kB of additional disk space will be used.
Do you want to continue [Y/n]? y
[**DELETED LINES**]
Setting up libpq-dev (9.1.15-0ubuntu0.12.04) ...
Setting up libssl-doc (1.0.1-4ubuntu5.27) ...
Processing triggers for libc-bin ...
ldconfig deferred processing now taking place
[root@whatever ~]#
[root@whatever ~]#
[root@whatever ~]# # OK Looks like libpq-dev is installed.
[root@whatever ~]# # Lets see if we hav been successful
[root@whatever ~]# #    Go back to joed.
[root@whatever ~]#
[root@whatever ~]# su joed
[joed@whatever ~]$ cd [path to SQL dir ie whatever dir has your C code in it]
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # Via WinSCP, I changed all permissions recursively to 755 Group: joed [1000] Owner: joed [1000] in [path to]/trial_test_matrix
[joed@whatever SQL]$ # see: http://www.postgresql.org/docs/9.1/interactive/auth-methods.html#AUTH-TRUST
[joed@whatever SQL]$ # see  http://www.postgresql.org/docs/9.3/static/libpq-connect.html
[joed@whatever SQL]$ #     added joed's pw for the omnia db to the connect string in tse_analogous_db.c
[joed@whatever SQL]$
[joed@whatever SQL]$ # OK lets Compile, Link & run tse_analogous_db
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # Compile tse_analogous_db.c
[joed@whatever SQL]$ gcc -c  -I/usr/include/postgresql  tse_analogous_db.c
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # Link tse_analogous_db.o into tse_analogous_db.exe
[joed@whatever SQL]$ gcc -o tse_analogous_db.exe tse_analogous_db.o -L/usr/lib -lpq
[joed@whatever SQL]$
[joed@whatever SQL]$
[joed@whatever SQL]$ # Run tse_analogous_db.exe
[joed@whatever SQL]$ ./tse_analogous_db.exe
datname        datdba         encoding       datcollate     datctype       datistemplate  datallowconn   datconnlimit   datlastsysoid  datfrozenxid   dattablespace  datacl

template1      10             6              en_US.UTF-8    en_US.UTF-8    t              t              -1             11942          706            1663           {=c/postgres,postgres=CTc/postgres}
template0      10             6              en_US.UTF-8    en_US.UTF-8    t              f              -1             11942          706            1663           {=c/postgres,postgres=CTc/postgres}
postgres       10             6              en_US.UTF-8    en_US.UTF-8    f              t              -1             11942          706            1663
omnia          10             6              en_US.UTF-8    en_US.UTF-8    f              t              -1             11942          706            1663


joe_t1 begin
id             test_ix        origin         max_prime      primes_tail_0  primes_tail_1  primes_tail_2  primes_tail_3  primes_tail_4  primes_tail_5  primes_tail_6  primes_tail_7

17             16             Aberdeen                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        830            0              0              0              0              0              0              0              0
func: func_uses_DAL_sub0, test_index: 0


func_uses_DAL_sub0.PQntuples(res): 1


[joed@whatever SQL]$
[joed@whatever SQL]$ # yahhoo SUCCESS
[joed@whatever SQL]$

Thus Endeth the Lesson 🙂

Love and peace,

Joe

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s