Re: Transitioning to a SQL db

Hi Tim,

As arranged I am cc-ing the pgsql-general list in the hope
they will assist.  Your posts to the list may be delayed for
moderation, I can't say.

It could be helpful if you subscribed to the list, but it
is relatively high traffic and I know you have extremely limited
and expensive bandwidth.  For this reason I'm not sure I can
recommend subscribing.

On Thu, 7 Apr 2016 12:22:26 +0200
Tim Vink <timvink@gmail.com> wrote:

> Many thanks for your kind offer to give us some advice. I am Tim Vink,
> Research Techician at the Kalahari Research Trust and currently
> Database and Networks manager of the project. Our Project leaders,
> Chris and Laura at the Meerkat project mainly involved with the main
> Meerkat database that is currently in access, where Chris is helping
> me develop the additional databases (that are currently loose db
> and/or files (read up to 80000 csv or mapsource files) and make a
> coherent structure for these.
>
> We are in the starting position and started off with MySQL using the
> Percona Server variant for more advanced / easy replication and
> databackups, would you recommend to move to PostGreSQL, what would be
> our main advantages over MySQL?

I no longer keep up with the MySQL feature set, and can't be
considered an expert in the overall status of databases
in the FOSS world.  But I am not entirely disconnected either
so should be of some help.

I can't give you a point-by-point comparison of MySQL and Postgres.
Overall, the difference is in approach.  The goal of Postgres
is to be SQL standards compliant, to be well designed,
to be ACID compliant, and to be reliable.

The attention to design is most significant.  MySQL was written
as glue, layering an SQL interface on top of a number of underlying
database engines.  And they were looking for speed.  ACID compliance
was something that came later.  As a result, in MySQL there are
myriad corner cases and rough edges.  Postgres does not have these.
This really starts to matter for people who don't spend all their
time living cuddled up to their database and getting to know
all it's quirks.

Postgres takes the time to ensure each new feature is "clean".
Although the emphasis is not on performance, this also includes
performance.  Again reliability is paramount.  Postgres goes to
great lengths to ensure that the data in the database is
never corrupted.  I already mentioned that it won't restore
a database dump unless the resulting referential integrity is intact.

A few minor examples:  PG distinguishes
between an empty string and NULL, the indeterminate data value.
Most other dbs represent the empty string as a string containing
a single space, and there is no distinction between an empty
string and a single space.  You can, with care, construct views in
PG that act in every way like regular tables, they can be updated,
inserted into, deleted from, etc.  This is a great boon when
users, such as in your case, work directly with the db.  The
PG SQL variant is a "clean", "typical" variant -- generally tracking
Oracle.  (The SQL standard is huge and awful and allows
just about anything that any major vendor wanted to cram in.)
There are no wierdo ways to write SQL that supply hints as to
how to optimize the query, the PG query optimizer does the
right thing for you.  PG has nothing like the variation
in SQL case sensitivity depending on underlying OS platform
like you find in MySQL.  In PG transactions apply everywhere,
even to meta-data like table creation.  This is unusual,
and very useful when making test alterations to a test
database as it eliminates error-prone cleanup on failure
and consequential lack of synchronization between test and
production databases.

The PG "window functions" (see the PG docs) are also incredibly
powerful for data analysis.  As is the ability to embed
R (r-project.org) into Postgres.  (Although embedding R
is dangerous from a security standpoint since it's then
reasonably impossible to prevent a PG user from writing
arbitrary files to disk.  This breaks the barrier between
db access and OS access.)

You may also be interested in PostGIS for geospatial
integration.  (postgis.org)
It is "way cool".

Regards Percona Server, it's FAQ says it tracks Oracle's MySQL.
I see the open source community moving away from Oracle's
MySQL to MariaDB and this could be a long-term concern.

> I take great example in your babase and ambaselli baboon project work.
> Where we have started (still very very much under development) a
> meerkat wiki. (meerkat.kalahariresearch.org)

You may also want to look at gombemi.ccas.gwu.edu for some
work I did for some of the Jane Goodall folk.  It has
some more advanced, from the standpoint of program
internals, web-based tools.

We use 2 idioms extensively.  The first is batch upload
from csv files.  This makes converting data to electronic
form, usually via MS Excel, a low-skill task.
We upload with custom programs, and with
generic uploads to tables and views.  We have a generic upload
program that ensures you get error messages for each line uploaded.
We also have a wwwdiff program for paranoid data checking.
(Have 2 people enter the data, independently, and then compare the
result before upload to find typos.)  Since our users interact with the
database directly our data integrity is checked via an
extensive set of triggers, ensuring that common data errors
never get in the db.   (Of course there can always be
typos in entered data.)

Our users use a data maintenance idiom for SQL manipulation
that looks like: begin transaction, make changes, run some queries
to test changes, rollback transaction.   The
SQL is submitted in a single "hunk" to the db. This is repeated until
the data looks right and then the final rollback is changed
to a commit.  This idiom presents problems with some GUI front-ends.
We had to modify phpPgAdmin to support it.  You might want
to look at adminer (www.adminer.org), which does support the
idiom.  (Coming from MS Access you may also find the adminer
graphical GUI query builder attractive.  As a rule I find
these sorts of things more dangerous than not -- users
tend to write queries they don't understand -- but there's
surely good potential.)

In general, there's no comprehensive web based solution for
PG that "does everything".  E.g.  Most tools will want to stop
after the first error on data upload.  Some will refuse
to upload into views.  Etc.  You can try using the Babase
tools if you like.  I have aspirations to build something
better for generic data upload/download but they remain in
the planning stage for the forseeable future.  (See also:
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools
)

You are of course welcome to our hacked phpPgAdmin.

If you get a chance (and use PG) you should probably utilize
the Gombe-MI/Babase warning system.  (I should really
package this separately, somehow.)  It's a batch oriented
data integrity checking tool based on user-supplied
SQL queries that look for trouble.  See the on-line docs.

As far as a web stack goes I gravitate toward the following:
Linux, Nginx, PostgreSQL, Python, Pyramid, SQLAlchemy, Mako, Bootstrap,
Bootswatch.  I used to work in PHP, but that was more because
there used to be few alternatives regards scripting for the
web.

As an FYI, I find Debian to be more stable than Ubuntu.  It
also integrates very nicely with PG.  The official PG repos
are one of the very very few non-Debian repos which are
recommended for inclusion in Debian.

Since you're Internet-limited you may also find rsync
interesting.  (Hardlinked backups rock.)
I have an rsync based backup script you
are welcome to, but it serves my specific needs.  It
is often better to go with a more generic even if less
serviceable tool like rsnapshot.

> I would be happy to have a skype conversation or continue our
> conversation over email / skype chat.

FYI to the list, we will take our chat to #postgresql
if and when we chat.

Tim, if you do decide to try PG go to #postgresql for help
with the initial configuration.  You'll want to configure
PG so that 40% of RAM (up to 4G?) is shared memory
and used by PG.  And adjust work_mem accordingly.
(And if the box does nothing but PG it's sometimes
useful to configure as much ram as possible as shared
memory.)  It can also be confusing to setup permissions
for database access the first time you do it.

(We give each user his own login, and his own schema
for personal work.  Some users get 2 logins, one
for regular work and one "superuser" login for
special purposes like creating new users.  Each
login is in one of 2 groups.  There's a read-only
group and a read-write group.  These groups
apply to the master, production, schema.
The read-write group does not get to alter
db structure, just alter table content.
People can do whatever they want in their
own schemas.)

To end, since you're coming off of MS Access, you may
not have a lot of experience in the FLOSS world.  One
key to success is picking the right components. Unlike
in the proprietary world, and excepting distros, there are few
one-stop shopping projects.  Individual projects are typically
tailored to doing one thing and doing it well.  You add
more projects into your installed mix to add additional
functionality.  Ask the community to find components.

Regards,

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein