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

2016-05-22 Thread J. Roeleveld 
Longer answer (as promised)

On Saturday, May 21, 2016 04:56:18 PM waltd...@waltdnes.org wrote:
> On Sat, May 21, 2016 at 08:55:39AM +0200, J. Roeleveld wrote

> > Did you run
> > emerge --config dev-db/postgresql:9.5
> 
>   Yes.
> 
> > succesfully?
> 
>   Obviously not.

:)

>   I think I've finally figured it out.  I edited
> /etc/conf.d/postgresql-9.5 to PG_INITDB_OPTS="--encoding=UTF8" and ran
> "emerge --config dev-db/postgresql:9.5".  I got an error message about
> the data directory not be empty (probably from my first attempt).  I ran
> 
> rm /var/lib/postgresql/9.5/data/*
> emerge --config dev-db/postgresql:9.5
> 
> and got a bit further.  I did get error messages as follows
> 
> 
> ###
> The database cluster will be initialized with locale "en_US.iso88591".
> initdb: encoding mismatch
> The encoding you selected (UTF8) and the encoding that the
> selected locale uses (LATIN1) do not match.  This would lead to
> misbehavior in various character string processing functions.
> Rerun initdb and either do not specify an encoding explicitly,
> or choose a matching combination.
> 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
> ###

If there is a mismatch, the config-script refuses to work correctly.

>   I fixed that.  In /etc/conf.d/postgresql-9.5 I set
> PG_INITDB_OPTS="--encoding=iso88591"  and ran
> 
> rm /var/lib/postgresql/9.5/data/*
> emerge --config dev-db/postgresql:9.5
> 
> and got the following.  Does it look OK?  Do I understand correctly...
> 
> config files are located in /etc/postgresql-9.5/
> the actual databases are located in /var/lib/postgresql/9.5/data
> 
> ###



> 
> WARNING: enabling "trust" authentication for local connections
> You can change this by editing pg_hba.conf or using the option -A, or
> --auth-local and --auth-host, the next time you run initdb.

This is nothing to worry about, as long as the database is not accessible from 
outside.
The "trust" part means it ignores passwords. By default, this is only for 
"localhost"

> Success. You can now start the database server using:
> 
> /usr/lib64/postgresql-9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/data -l
> logfile start

Ignore this line.

>  * The autovacuum function, which was in contrib, has been moved to the main
> * PostgreSQL functions starting with 8.1, and starting with 8.4 is now
> enabled * by default. You can disable it in the cluster's:
>  * /etc/postgresql-9.5/postgresql.conf
>  *
>  * The PostgreSQL server, by default, will log events to:
>  * /var/lib/postgresql/9.5/data/postmaster.log
>  *
>  * You should use the '/etc/init.d/postgresql-9.5' script to run PostgreSQL
>  * instead of 'pg_ctl'.

Listen to this line :)
The init-script uses "pg_ctl" to start/stop postgresql.

> ###
> 
>   There's still one apparent internal contradiction in the output...
> 
> ###
> Success. You can now start the database server using:
> 
> /usr/lib64/postgresql-9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/data -l
> logfile start
> ###
> 
> ...but it also says...
> 
> ###
>  * You should use the '/etc/init.d/postgresql-9.5' script to run PostgreSQL
>  * instead of 'pg_ctl'.
> ###

See above, use the init-script and you're fine.

If you change the configuration, you can tell Postgresql to reload the new 
config by issuing " /etc/init.d/postgresql-9.5 reload "

Most common config-changes to a running system: adding/removing users/access to 
/etc/postgresql-9.5/pg_hba.conf.

One additional piece of info, by default, it logs to
/var/lib/postgresql/9.5/data/postmaster.log

If you want it to log to a different location or to syslog, you can edit this 
in the file:
/etc/postgresql-9.5/postgresql.conf

Look for the section:
#--
# ERROR REPORTING AND LOGGING
#--

The comments in the file tell you which settings require a restart (of 
postgresql). The ones that don't should come into effect with just the 
"reload" command I mentioned above.

--
Joost

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

2016-05-22 Thread J. Roeleveld 
On Sunday, May 22, 2016 01:30:32 AM waltd...@waltdnes.org wrote:
> On Sun, May 22, 2016 at 04:41:44AM +, J. Roeleveld wrote
> 
> > Quick reply (longer one later)
> > 2nd run looks better.
> > 
> > The output is from the supplied scripts. For Gentoo, use the
> > /etc/init.d script. That will call the other one where necessary.
> > 
> > About the codepage. What does "eselect locale list" show?
> > In other words, which locale do you actually use?
> 
>   en_US.iso88591
> 
> [i3][waltdnes][~] eselect locale list
> Available targets for the LANG variable:
>   [1]   C
>   [2]   POSIX
>   [3]   en_US
>   [4]   en_US.iso88591 *
>   [5]   en_US.utf8
>   [ ]   (free form)
> 
>   I expect to be storing a lot of numeric data in postgresql, but not
> much text data, let alone, non-English text data.  I live in Toronto,
> Canada.  ISO8859-1 can handle ASCII (English), and accented Latin-1
> characters that exist in Canadian French.

In order for the configuration to actually be able to configure the database to 
handle UTF8, you need to run at least UTF8 or a superset.
I always run my servers in UTF8.

If you will not require non-ISO8859-1 characters, you should be ok with your 
current setup.

--
Joost

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

2016-05-21 Thread waltdnes 
On Sun, May 22, 2016 at 04:41:44AM +, J. Roeleveld wrote
> 
> Quick reply (longer one later)
> 2nd run looks better. 
> 
> The output is from the supplied scripts. For Gentoo, use the
> /etc/init.d script. That will call the other one where necessary.
> 
> About the codepage. What does "eselect locale list" show?
> In other words, which locale do you actually use?

  en_US.iso88591

[i3][waltdnes][~] eselect locale list
Available targets for the LANG variable:
  [1]   C
  [2]   POSIX
  [3]   en_US
  [4]   en_US.iso88591 *
  [5]   en_US.utf8
  [ ]   (free form)

  I expect to be storing a lot of numeric data in postgresql, but not
much text data, let alone, non-English text data.  I live in Toronto,
Canada.  ISO8859-1 can handle ASCII (English), and accented Latin-1
characters that exist in Canadian French.

-- 
Walter Dnes 
I don't run "desktop environments"; I run useful applications

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

2016-05-21 Thread J. Roeleveld 
On May 21, 2016 10:56:18 PM GMT+02:00, waltd...@waltdnes.org wrote:
>On Sat, May 21, 2016 at 08:55:39AM +0200, J. Roeleveld wrote
>> Longer answer:
>> 
>> On Friday, May 20, 2016 10:36:41 PM waltd...@waltdnes.org wrote:
>> >   Yes, I did RTFM at
>https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
>> > and that's part of my problem.   I figured it would be a simple
>> > search and replace "9.3" ==> "9.5" in the wiki, but...
>> 
>> A quick scan should indicate that.
>> However:
>> PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8"
>> is wrong. See below.
>> 
>> > 1) The wiki recommends...
>> > PG_INITDB_OPTS="--locale=en_US.UTF-8"
>> 
>> Where did you configure this?
>> 
>> I did the following:
>> # cat /etc/conf.d/postgresql-9.5 | grep -i utf
>> PG_INITDB_OPTS="--encoding=UTF8"
>> 
>> 
>> Did you run 
>> emerge --config dev-db/postgresql:9.5
>
>  Yes.
>
>> succesfully?
>
>  Obviously not.  I think I've finally figured it out.  I edited
>/etc/conf.d/postgresql-9.5 to PG_INITDB_OPTS="--encoding=UTF8" and ran
>"emerge --config dev-db/postgresql:9.5".  I got an error message about
>the data directory not be empty (probably from my first attempt).  I
>ran
>
>rm /var/lib/postgresql/9.5/data/*
>emerge --config dev-db/postgresql:9.5
>
>and got a bit further.  I did get error messages as follows
>
>
>###
>The database cluster will be initialized with locale "en_US.iso88591".
>initdb: encoding mismatch
>The encoding you selected (UTF8) and the encoding that the
>selected locale uses (LATIN1) do not match.  This would lead to
>misbehavior in various character string processing functions.
>Rerun initdb and either do not specify an encoding explicitly,
>or choose a matching combination.
>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
>###
>
>  I fixed that.  In /etc/conf.d/postgresql-9.5 I set
>PG_INITDB_OPTS="--encoding=iso88591"  and ran
>
>rm /var/lib/postgresql/9.5/data/*
>emerge --config dev-db/postgresql:9.5
>
>and got the following.  Does it look OK?  Do I understand correctly...
>
>config files are located in /etc/postgresql-9.5/
>the actual databases are located in /var/lib/postgresql/9.5/data
>
>###
>[i3][root][~] emerge --config dev-db/postgresql:9.5
>
>Configuring pkg...
>
> * You can modify the paths and options passed to initdb by editing:
> * /etc/conf.d/postgresql-9.5
> * 
> * Information on options that can be passed to initdb are found at:
> * http://www.postgresql.org/docs/9.5/static/creating-cluster.html
> * http://www.postgresql.org/docs/9.5/static/app-initdb.html
> * 
> * PG_INITDB_OPTS is currently set to:
> * --encoding=UTF8
> * 
> * Configuration files will be installed to:
> * /etc/postgresql-9.5/
> * 
> * The database cluster will be created in:
> * /var/lib/postgresql/9.5/data
> * 
> * Are you ready to continue? (y/n)
>y
> * Creating the data directory ...
> * Initializing the database ...
>The files belonging to this database system will be owned by user
>"postgres".
>This user must also own the server process.
>
>The database cluster will be initialized with locale "en_US.iso88591".
>The default text search configuration will be set to "english".
>
>Data page checksums are disabled.
>
>fixing permissions on existing directory /var/lib/postgresql/9.5/data
>... ok
>creating subdirectories ... ok
>selecting default max_connections ... 100
>selecting default shared_buffers ... 128MB
>selecting dynamic shared memory implementation ... posix
>creating configuration files ... ok
>creating template1 database in /var/lib/postgresql/9.5/data/base/1 ...
>ok
>initializing pg_authid ... ok
>initializing dependencies ... ok
>creating system views ... ok
>loading system objects' descriptions ... ok
>creating collations ... ok
>creating conversions ... ok
>creating dictionaries ... ok
>setting privileges on built-in objects ... ok
>creating information schema ... ok
>loading PL/pgSQL server-side language ... ok
>vacuuming database template1 ... ok
>copying template1 to template0 ... ok
>copying template1 to postgres ... ok
>syncing data to disk ... ok
>
>WARNING: enabling "trust" authentication for local connections
>You can change this by editing pg_hba.conf or using the option -A, or
>--auth-local and --auth-host, the next time you run initdb.
>
>Success. You can now start the database server using:
>
>/usr/lib64/postgresql-9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/data -l
>logfile start
>
>* The autovacuum function, which was in contrib, has been moved to the
>main
>* PostgreSQL functions starting with 8.1, and starting with 8.4 is now
>enabled
> *

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

2016-05-21 Thread waltdnes 
On Sat, May 21, 2016 at 08:55:39AM +0200, J. Roeleveld wrote
> Longer answer:
> 
> On Friday, May 20, 2016 10:36:41 PM waltd...@waltdnes.org wrote:
> >   Yes, I did RTFM at https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
> > and that's part of my problem.   I figured it would be a simple
> > search and replace "9.3" ==> "9.5" in the wiki, but...
> 
> A quick scan should indicate that.
> However:
> PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8"
> is wrong. See below.
> 
> > 1) The wiki recommends...
> > PG_INITDB_OPTS="--locale=en_US.UTF-8"
> 
> Where did you configure this?
> 
> I did the following:
> # cat /etc/conf.d/postgresql-9.5 | grep -i utf
> PG_INITDB_OPTS="--encoding=UTF8"
> 
> 
> Did you run 
> emerge --config dev-db/postgresql:9.5

  Yes.

> succesfully?

  Obviously not.  I think I've finally figured it out.  I edited
/etc/conf.d/postgresql-9.5 to PG_INITDB_OPTS="--encoding=UTF8" and ran
"emerge --config dev-db/postgresql:9.5".  I got an error message about
the data directory not be empty (probably from my first attempt).  I ran

rm /var/lib/postgresql/9.5/data/*
emerge --config dev-db/postgresql:9.5

and got a bit further.  I did get error messages as follows


###
The database cluster will be initialized with locale "en_US.iso88591".
initdb: encoding mismatch
The encoding you selected (UTF8) and the encoding that the
selected locale uses (LATIN1) do not match.  This would lead to
misbehavior in various character string processing functions.
Rerun initdb and either do not specify an encoding explicitly,
or choose a matching combination.
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
###

  I fixed that.  In /etc/conf.d/postgresql-9.5 I set
PG_INITDB_OPTS="--encoding=iso88591"  and ran

rm /var/lib/postgresql/9.5/data/*
emerge --config dev-db/postgresql:9.5

and got the following.  Does it look OK?  Do I understand correctly...

config files are located in /etc/postgresql-9.5/
the actual databases are located in /var/lib/postgresql/9.5/data

###
[i3][root][~] emerge --config dev-db/postgresql:9.5

Configuring pkg...

 * You can modify the paths and options passed to initdb by editing:
 * /etc/conf.d/postgresql-9.5
 * 
 * Information on options that can be passed to initdb are found at:
 * http://www.postgresql.org/docs/9.5/static/creating-cluster.html
 * http://www.postgresql.org/docs/9.5/static/app-initdb.html
 * 
 * PG_INITDB_OPTS is currently set to:
 * --encoding=UTF8
 * 
 * Configuration files will be installed to:
 * /etc/postgresql-9.5/
 * 
 * The database cluster will be created in:
 * /var/lib/postgresql/9.5/data
 * 
 * Are you ready to continue? (y/n)
y
 * Creating the data directory ...
 * Initializing the database ...
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.iso88591".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/9.5/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/9.5/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/lib64/postgresql-9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/data -l 
logfile start

 * The autovacuum function, which was in contrib, has been moved to the main
 * PostgreSQL functions starting with 8.1, and starting with 8.4 is now enabled
 * by default. You can disable it in the cluster's:
 * /etc/postgresql-9.5/postgresql.conf
 * 
 * The PostgreSQL server, by default, will log events to:
 *

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

2016-05-21 Thread Alec Ten Harmsel 



On 2016-05-21 07:32, J. Roeleveld wrote:

On Saturday, May 21, 2016 06:51:46 AM Alec Ten Harmsel wrote:


`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.

Does this finally work?
Last time I tried this, half the functionality didn't work at all and the
other half was buggy. (This was years ago)


I have no idea, but I'm going to test in a VM because I'm a little 
curious now.



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.

For script examples:
http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file


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.

4. Write it all in C++ :)


Qt and QCustomPlot are nice, but I'm not sure I have quite that much time.


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.

Something I could use (and others):
A simple PHP page which I can feed:
- connection parameters to a database
- select-query
- which result-field to use for the horizontal axis
and then plots the remaining fields for the vertical axis.

I haven't checked with google yet, so if there is a decent example, I'd be
interested :)

--
Joost



Google gave me nothing. I have not written PHP for a long time, and I'm 
so unfamiliar with deploying/running PHP that it would take me a long 
time to write this.


Another option is R - I just did some searching, and it supports pulling 
data from a database. R's basic plotting functions are real nice. I 
imagine a script could do some basic queries + plotting in 20-30 lines.


Alec

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

2016-05-21 Thread J. Roeleveld 
On Saturday, May 21, 2016 06:51:46 AM Alec Ten Harmsel wrote:
> Joost knows far more about databases than I do, so I mostly commented on
> the workflow part.
>
> On 2016-05-20 22:36, waltd...@waltdnes.org wrote:



> 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.

There are symlinks from the /var location to /etc for the configuration files.
The data itself, eg. PGDATA, sits, by default, in /var/.


> `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.

Does this finally work?
Last time I tried this, half the functionality didn't work at all and the 
other half was buggy. (This was years ago)

> >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.

For script examples:
http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file

> 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.
4. Write it all in C++ :)

> 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.

Something I could use (and others):
A simple PHP page which I can feed:
- connection parameters to a database
- select-query
- which result-field to use for the horizontal axis
and then plots the remaining fields for the vertical axis.

I haven't checked with google yet, so if there is a decent example, I'd be 
interested :)

--
Joost

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

2016-05-21 Thread 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, waltd...@waltdnes.org wrote:

   Yes, I did RTFM at https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
and that's part of my problem.   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

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

2016-05-21 Thread J. Roeleveld 
Longer answer:

On Friday, May 20, 2016 10:36:41 PM waltd...@waltdnes.org wrote:
>   Yes, I did RTFM at https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
> and that's part of my problem.   I figured it would be a simple
> search and replace "9.3" ==> "9.5" in the wiki, but...

A quick scan should indicate that.
However:
PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8"
is wrong. See below.

> 1) The wiki recommends...
> PG_INITDB_OPTS="--locale=en_US.UTF-8"

Where did you configure this?

I did the following:
# cat /etc/conf.d/postgresql-9.5 | grep -i utf
PG_INITDB_OPTS="--encoding=UTF8"


> ...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

Postgresql only uses the codepage, not the localisation ("en_US") part.

> 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?

Did you run 
emerge --config dev-db/postgresql:9.5
succesfully?

>   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.

There are ODBC and native drivers. You need to check which have support 
directly. Look for "postgres" USE-flags in spreadsheet applications.

>   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?

Not to my knowledge, I tend to use spreadsheets or graphics libraries in C++ 
GUI applications. (Still playing with the latter, so not the best resource for 
that)

>   I used to work with Oracle and PL/SQL before I retired, so I think I
> know what I'm getting into as far as the database stuff is concerned.
> Once I get past the Gentoo-specific install problems, I'll subscribe to
> a postgresql mailing list, and ask postgresql-specific questions there.

Postgresql has it's own procedural language, might be nice to look into that 
in that case.

I would suggest the USER-mailing list.
The development one (HACKERS) deals with the actual internals, not something 
most users would be interested in.

--
Joost

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

2016-05-20 Thread J. Roeleveld 
On May 21, 2016 4:36:41 AM GMT+02:00, waltd...@waltdnes.org wrote:
>  Yes, I did RTFM at https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart
>and that's part of my problem.   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?
>
>  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.
>
> 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?
>
>  I used to work with Oracle and PL/SQL before I retired, so I think I
>know what I'm getting into as far as the database stuff is concerned.
>Once I get past the Gentoo-specific install problems, I'll subscribe to
>a postgresql mailing list, and ask postgresql-specific questions there.

Quick response. Longer one later when I have access to the database server.

I don't use the wiki, instead I follow the post-emerge messages.

UTF8 is possible. I only use that.
Not sure about gnumeric, bit libreoffice can read directly from postgresql.
ODBC is not as simple on Linux as it is on Windows. But when configured, it 
should work as well. 
Exporting data to CSV is mentioned in several howtos and in the manpage of psql 
(commandline tool)

--
Joost 
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.