PostgreSQL (or postgres for short) is the database backend used by SQL-Ledger. It is a less know but powerful RDBMS having functionality and features comparable to high-end commercial databases like Oracle. In this short tutorial we will cover the basic PostgreSQL functionality which is required to run the SQL-Ledger smoothly.
PostgreSQL can be installed on with complete or custom installation of the operating system. Once installed you can start the database service with 'service postgresql start' and have it start at boot time with 'chkconfig postgresql on' commands issued from shell as root.
On FreeBSD, you can install it from ports. You have the choice of install either 7.4.xx or 8.x.x version. I would recommend to go with 8.2.x because of better auto-vacuuming functionality.
Following commands will install the postgres and make it available on next reboot:
cd /usr/ports/databases/postgresql82-server/ make make install
Edit your /etc/rc.conf to enable the postgres:
postgresql_enable='YES'
Initialise database:
/usr/local/etc/rc.d/postgresql initdb
Start database service:
/usr/local/etc/rc.d/postgresql start
Postgresql allows many authentication methods for connecting to it locally or over the network through an application or through the bundled client tools like psql.
Authentication is configured in /var/lib/pgsql/data/pg_hba.conf (Redhat) or in /usr/local/pgsql/data/pg_hba.conf (FreeBSD)
Following line in the pg_hba.conf will allow you to connect to Postgres without asking for any password.
local all all trust
Be sure to restart the postgresql service after this change. This many not a very secure setup for your environment but it helps install and start using SQL-Ledger without much fuss.
A more secure setup follows. To keep things simple and secure, I am assuming that you are using a dedicated machine for sql-ledger (that is, without any other OS users) and you also want to have access to your data through phpPgAdmin. For a resonably secure and simple setup, follow these instructions:
psql -U pgsql template1 ALTER USER pgsql WITH PASSWORD 'mygoodpassword';
local all all password
[root@freebsd ~]# createuser -U pgsql -P ledger Enter password for new role: Enter it again: 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) n CREATE ROLE
psql is a command line client application to connect to postgres and issue various commands, much like mysql for MySQL and sqlplus for Oracle. To connect to database you issue a command something like this:
psql -U postgres template1 # redhat psql -U pgsql template1 # freebsd
Once you are connected, you will land on a command shell which will look something like this:
Welcome to psql 8.2.1, 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 template1=#
Once you are in command shell, you can execute any SQL command (like SELECT accno, description FROM chart or any of the psql commands. You can display help with \? or \h. Here I list some of useful commands along with a short description:
\connect
connect to new database (currently “template1”)
\q
quit psql
\w FILE
write query buffer to file
\i FILE
execute commands from file
\o [FILE]
send all query results to file or |pipe
\d [NAME]
describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN]
list tables/indexes/sequences/views/system tables
\l
list all databases
Two useful web-based interfaces to Postgess are available. One is popular phpPgAdmin and the other is Webmin. Webmin is a general purpose server management software which includes a module to manage postgres databases among other things.
Postgres comes configured with a blank database called template1. Whenever you create a new database, all objects (tables, triggers, functions etc) are copied from template1 to the new database. This is a great convenience. For example you could setup a complete sql-ledger dataset with chart of accounts etc into template1. Whenever you needed a new dataset for a new company, you could just create a new database and it would be ready for use with sql-ledger.
At the minimum we create the plpgsql procedural language into this database before creating our datasets which allows us to make it available in all databases we create subsequently. plpgsql is an essential requirement of sql-ledger to operate properly. A simple command like the following does the job:
createlang -U postgres plpgsql template1
Important: Run this createlang command on template1 before creating any databases for use with SQL-Ledger. If you have already created a database for SQL-Ledger, then run the createlang command on that database too.
A database contains an independent set of tables, indices, functions etc. To run sql-ledger for multiple companies, we can create multiple databases each with its own chart of accounts, parts, items, invoices etc.
To create a new database:
createdb -U postgres data2006
To drop any existing database:
dropdb -U postgres data2006 # warning: all data will be lost unless you have a backup.
There are few other client programs (other than psql) which are helpful in interacting with Postgres. These are described below:
createlang allows you to install a particular procedural language into the database. Procedural lanages are used to do server-side procedures and triggers. SQL-Ledger utilises the plpgsql procedure language to write triggers and functions.
createdb allows you to create a new postgres database
dropdb drops any existing database.
createuser adds a new user to the postgres system.
pg_dump dumps the given database contents to standard output
pg_dumpall dumps the all database contents with to standard output
vacuumdb cleans-up and de-fragments the given database
You can create your sql-ledger dataset yourself instead of creating them using admin.pl. Follow these steps to accomplish this.
# Move to sql folder cd /usr/local/sql-ledger/sql/ # Create a database createdb -U postgres data2008 # Create tables, indices and plpgsql functions psql -U postgres data2008 < Pg-tables.sql psql -U postgres data2008 < Pg-indices.sql psql -U postgres data2008 < Pg-functions.sql psql -U postgres data2008 < Default-chart.sql
Now you can login to admin.pl and create users for sql-ledger using 'data2008' as database for the users.
Sometimes it is enough to just untar the latest sql-ledger version over your installed one to upgrade. Any dataset upgrades will be done when you login first time after upgrade.
Sometimes, however, you might need to apply the database upgrades yourself. Here is what you need to do:
To do this look into the defaults table see your database version. Now run the Pg-upgrade-oldversion-newversion.sql files starting from your current database version.
Please note that database version and application versions might be different for a given release. For example sql-ledger-2.8.11 database version is 2.8.8. This simply means that there is no database change since 2.8.8.
Important: Always backup your datasets before upgrading.
Let us say you determine you have 2.8.4 version by looking at defaults table of your dataset. So you run the following commands:
psql -U postgres mydataset < Pg-upgrade-2.8.4-2.8.5.sql psql -U postgres mydataset < Pg-upgrade-2.8.5-2.8.6.sql psql -U postgres mydataset < Pg-upgrade-2.8.6-2.8.7.sql psql -U postgres mydataset < Pg-upgrade-2.8.7-2.8.8.sql
This will bring your datasets to the latest database version.