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]$