Re: [gentoo-user] postgresql 9.5.2 versus Gentoo wiki install instructions?

[Alec Ten Harmsel]

Joost knows far more about databases than I do, so I mostly commented on 
the workflow part.

On 2016-05-20 22:36, waltdnes@××××××××.org wrote:
>    Yes, I did RTFM at https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
> and that's part of my problem. <G>  I figured it would be a simple
> search and replace "9.3" ==> "9.5" in the wiki, but...
>
> 1) The wiki recommends...
> PG_INITDB_OPTS="--locale=en_US.UTF-8"
>
> ...but I get...
>
>> The database cluster will be initialized with locale "en_US.iso88591".
>> initdb: "en_US.UTF8" is not a valid server encoding name
> "locale -a" returns...
> C
> POSIX
> en_US
> en_US.iso88591
> en_US.utf8
>
> 2) The wiki says...
>> This time the focus is upon the files in the PGDATA directory,
>> /etc/postgresql-9.3 , instead with primary focus on the
>> postgresql.conf and pg_hba.conf files.
> "ls /etc/postgresql-9.5/" returns...
> postgresql.conf  psqlrc
>
> but postgresql seems to want them in /var/lib instead...
>
>> mv: cannot stat '/var/lib/postgresql/9.5/data/pg_hba.conf': No such
>> file or directory
>> mv: cannot stat '/var/lib/postgresql/9.5/data/pg_ident.conf': No
>> such file or directory
>> mv: cannot stat '/var/lib/postgresql/9.5/data/postgresql.conf':
>> No such file or directory
>    Can somebody please confirm the correct way to go?

I have never run postgresql on gentoo (hopefully soon :D), but on 
Debian-derived distros and RPM-based distros, PGDATA is always somewhere 
in /var. /etc seems wrong.

>
>    Why I want postgresql... I've been keeping a bunch of data in a
> spreadsheet, and it's gotten too large.  The spreadsheet locks up my
> system when I try to update it.  I've used "top" and watched as
> gnumeric's memory consumption grows to eat all available ram.  It locks
> up the system so I can't even ssh in.  This is on an X86_64 with 8 gigs
> of RAM!  Fortunately, "magic-sysrq" allows a relatively clean shutdown.
> While we're at it, is there a way for gnumeric to pull in data directly
> from postgresql?  ODBC?  I'm aware of copying from postgresql to a CSV
> file and importing that, but it's rather clunky.

`equery use gnumeric' gives the `libgda' flag, which should pull in 
database support. I've never used it, so I don't know whether or not it 
works/how well it works. What is in this spreadsheet? If it is financial 
stuff, you can use Gnucash, which supports using a database as a backend.

>
>    My main problem is that columns of several thousand rows are functions
> based on other columns of several thousand rows.  For the time-being,
> I've split up the spreadsheet into a few pieces, but a database is the
> best solution.  If I could run the calculations in the database, and
> pull in the final results as static numbers for graphing, that would
> greatly reduce the strain on the spreadsheet.  Or is it possible to
> graph directly from postgresql?

Here are my recommendations, in order of "least code" to "most code" (I 
don't think postgresql supports graphing):

1. Write some sql scripts that compute the data you need and output CSV, 
then import to Gnumeric and do the plots.
2. Write python script(s) that run SQL commands and plot the data with 
matplotlib.
3. Write a webapp so you don't have to run scripts by hand - the plots 
are generated by opening a web page.

Depending on how much automation you want vs. how much time you want to 
spend writing/debugging code, hopefully one of those helps. I help 
researchers use a HPC cluster; some are very savvy programmers, some are 
not. For working on "big data" projects, some will throw raw data into a 
Hadoop cluster and happily do all their work using Hadoop, while some 
will put in raw data, clean it up, and then pull it out and use MATLAB, 
stata, R, etc., so you just need to find the workflow that works best 
for you. I personally would choose option 3, as it involves the least 
amount of running scripts over and over, but to each his own.

I have actual free time now (done with school, finally), so I might be 
able to help prototype if you would like as well.

Alec