Re: [GENERAL] DATA Integrity & Recovery

[Rich Shepard]

On Wed, 12 Jul 2017, chris faber wrote:


I would appreciate the communities help in the following:

1. Determine if data from the incremental backups can be restored or
recovered.
2. Determine if data can be recovered from individual files backed up from
main Postgres data directory.


Chris,

  I am very far from being a postgres admin expert, but my understanding
from years ago is that general system backups (and I use dirvish every day
for incremental backups of all partitions) are different from
postgres-specific backups using pg_dump and pg_dumpall. I never looked into
the differences or why they mattered, but each week I run pg_dumpall and
save the .sql file in a database_backup/ directory.

  This does not answer your specific questions but does offer a strategy for
future operations.

HTH,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] INSERT INTO: string with apostrophe

[Rich Shepard]

On Thu, 29 Jun 2017, Cachique wrote:


That is correct. You can double the single quotes. Another way is to use
the E'...' syntax (i.e., E'O\'Brien'). Or you can use the quote_*
functions (
https://www.postgresql.org/docs/current/static/functions-string.html)


Walter,

  Thanks for confirming.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] INSERT INTO: string with apostrophe

[Rich Shepard]

  The syntax for inserting data into a table uses single quotes to identify
strings. When I have a string such as O'Brien do I double the single quotes
within the string (i.e., 'O''Brien') or is there another way to include such
strings?

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres backup solution

[Rich Shepard]

On Tue, 14 Mar 2017, Adrian Klaver wrote:


What would be a recommended solution for backing up a very large Postgres
(~13TeraBytes) database in order to prevent from data deletion/corruption.
Current setup is only to backup/restore to a standby read-only Postgres
server via AWS S3 using wal-e however this does not offer the comfort of
keeping a full backup available in case we need to restore some deleted or
corrupted data.


Still not coming through as plain text. You might want to talk to whoever is 
in charge of the email server.


Lawrence,

  A.) My MUA is alpine and your message displays just fine here.

  2.) I'm far from being a professional DBA but if I had to back up a 13T
database what I'd do (since I use only linux) is run pg_dump with the
archive (tar) format, then use dirvish to synchronize it with a remote copy.
Dirvish  uses rsync and records only changes since
the last run. I use it to back up my server/workstation daily. I've restored
files accidently deleted with no problems using either cp or rsync.

  That's my $0.25 worth (inflation, you know).

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Adrian Klaver wrote:


That this looks like a merge of the sample file:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/libpq/pg_hba.conf.sample;h=6b1778a72136edf52cea56f2ab088b9449df9a48;hb=HEAD
with your additions.



Seems you have not restarted Postgres since the last time you had a valid
pg_hba.conf file. The above is not one. Remove the @***@ strings and you
should be good.


Adrian,

  I accessed a postgres database (my bookkeeping data) yesterday and the
only difference from then to now was upgrading postgres one third-digit
version.

  Prior to today my upgrades were generally one or two minor versions; e.g.,
from 9.3.x to 9.5.x, and done with pg_dumpall and restoration using psql.
Today I just used pg_ctl to stop the server, ran the normal Slackware
upgradepkg routines, and tried to restart the server. I do not see a
pg_hba.conf file anywhere in the source or build trees so I've no idea from
where this unworkable version came.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start [FIXED]

[Rich Shepard]

On Fri, 10 Mar 2017, Tom Lane wrote:


The "@remove-line-for-nolocal@" bits should not be there. initdb would
normally either delete those lines entirely, or strip off
"@remove-line-for-nolocal@", depending on the switches it was given.
Likewise the various other @something@ bits should have been changed to
something else.

It certainly wasn't working before if it was like this, either.


Tom,

  I've no idea how the file was changed, but something certainly happened to
it. Restoring pg_hba.conf from 2016-09-17 (when pg-9.5.1 was installed)
certainly fixed the issue.

Many thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Rich Shepard wrote:


Actually, it has the modifications I've made over the years. That's why I
could not see what changed.


  Well, postgres was running yesterday and allowed me to access my
bookkeeping software so obviously something did change without my manually
editing pg_hba.conf. Here are the log records:

LOG:  could not open secondary authentication file "@authcomment@" as
"/var/lib/pgsql/9.6/data/authcomment@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file "@authmethodhost@" as
"/var/lib/pgsql/9.6/data/authmethodhost@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
LOG:  end-of-line before authentication method
CONTEXT:  line 86 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

  Postgres runs on my desktop server/workstation and I'm the only user
loggin in. Here's pg_hba.conf (restored from 2-22-2017 backup) which still
does not work. I've read the pg_hba.conf section in the 9.6 docs without
seeing what's wrong with my file.

# PostgreSQL Client Authentication Configuration File
# ===
# Put your actual configuration here
# --
@authcomment@

# TYPE  DATABASEUSERADDRESS METHOD

@remove-line-for-nolocal@# "local" is for Unix domain socket connections only
@remove-line-for-nolocal@local   all all
 @authmethodlocal@
# IPv4 local connections:
local   all rshepardtrust
hostall rshepard127.0.0.1/32trust
hostnossl all   rshepard127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 @authmethodhost@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@#local   replication @default_username@
@authmethodlocal@
#hostreplication @default_username@127.0.0.1/32
@authmethodhost@
#hostreplication @default_username@::1/128 
@authmethodhost@

  What am I not seeing?

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] INSERT and ON CONFLICT

[Rich Shepard]

On Fri, 10 Mar 2017, Brian Dunavant wrote:


I believe the following test should answer your question.


  Thank you, Brian. It does answer my question.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

On Fri, 10 Mar 2017, Tom Lane wrote:


It looks like what you have in pg_hba.conf is a raw copy of
pg_hba.conf.sample, without any of the editing that initdb normally
applies to it (to say nothing of manual adjustments you might make later).


Tom,

  Actually, it has the modifications I've made over the years. That's why I
could not see what changed.


It's hard to tell from the information given whether this is pilot error
or something broken in SlackBuilds' upgrade script.


  I've not modified the file in a while, and don't overwrite the existing
one when a new one is available. This is a new error for me.


In any case, you should be able to fix it by making a copy of your
pre-upgrade pg_hba.conf and shoving that back into the data directory
afterwards;


  I'll restore a backup copy from last month.

Thanks,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] INSERT and ON CONFLICT

[Rich Shepard]

  I'm filling a table with rows and have the first batch successfully
inserted. When I add more rows there may be some that already exist in the
table and I would prefer that they be ignored and the insert process
continue.

  The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is exactly
what I want to include in the command. Have I correctly interpreted what the
DO NOTHING option does when a row to be inserted already is present in the
table?

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgradede -9.6.1 to -9.6.2; postmaster will not start

[Rich Shepard]

  I've tried resolving this but do not see what changed.

  1. Shut down postgres:
pg_ctl stop -D /var/lib/pgsql/9.6/data

  2. Upgraded version (using SlackBuilds.org package as usual).

  3. Tried re-starting postgres:

postgres@salmo:~$ postgres -D /var/lib/pgsql/9.6/data/ &
[1] 17585
postgres@salmo:~$ LOG:  could not open secondary authentication file
"@authcomment@" as "/var/lib/pgsql/9.6/data/authcomment@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@local" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@local": No such file or
directory
LOG:  could not open secondary authentication file "@authmethodlocal@" as
"/var/lib/pgsql/9.6/data/authmethodlocal@": No such file or directory
LOG:  could not open secondary authentication file
"@remove-line-for-nolocal@" as
"/var/lib/pgsql/9.6/data/remove-line-for-nolocal@": No such file or
directory
LOG:  invalid connection type "all"
CONTEXT:  line 80 of configuration file
"/var/lib/pgsql/9.6/data/pg_hba.conf"
FATAL:  could not load pg_hba.conf
LOG:  database system is shut down

  4. Line 80 in pg_hba.conf:
@remove-line-for-nolocal@local   allall   @authmethodlocal@

  5. Tried commenting out that line to match backup, but that also did not
allow postgres to start.

  6. All files are owned by postgres.users.

  Please teach me how to fix this.

TIA,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/ [RESOLVED]

[Rich Shepard]

On Sat, 25 Feb 2017, Rich Shepard wrote:


Just installed postgresql-9.6.2 on Slackware-14.2/x86_64 and initialized
the database. However, there's no /usr/lib/postgresql/ directory with its
subdirectories.


  Duh! It's in /usr/lib64/postgresql.

Sigh,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/

[Rich Shepard]

On Sat, 25 Feb 2017, Adrian Klaver wrote:


What application?


  GRASS .


There is, it is just not at /usr/lib/postgresql. Search for plpgsql.so


  Ah! That's what I need to know.

As to the build you will either need to specify where the lib/ is in the 
configuration process or create a symlink .


  Well, sure. As long as I provide grass with the proper path in the config
file it's happy.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] New 9.6.2 installation lacks /usr/lib/postgresql/

[Rich Shepard]

  Just installed postgresql-9.6.2 on Slackware-14.2/x86_64 and initialized
the database. However, there's no /usr/lib/postgresql/ directory with its
subdirectories, and another application I'm building wants that location
during configuration.

  Postgres was installed via the SlackBuilds.org build script which I've
used for many years so I don't know where to start looking for the reason
there's no postgres lib/ directory.

  Diagnostic help appreciated.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY to question [ANSWERED]

[Rich Shepard]

On Tue, 17 Jan 2017, Tom Lane wrote:


Use psql's \copy instead.


  Thanks, Tom.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY to question

[Rich Shepard]

  Running -9.6.1. I have a database created and owned by me, but cannot copy
a table to my home directory. Postgres tells me it cannot write to that
directory. The only way to copy tables to files is by doing so as the
superuser (postgres).

  Why is this, and can I change something so I, as a user, can copy tables
directly to ~/?

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.6.1: INSERT with PK as serial

[Rich Shepard]

On Mon, 16 Jan 2017, David G. Johnston wrote:


Without any special effort on your part the first 40 records you try to
insert using the default sequence are now going to fail with duplicate key
errors.


David,

  Thank you. I added them while thrashing around looking for the source of
the problem. It's now fixed.

Much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.6.1: INSERT with PK as serial [FIXED]

[Rich Shepard]

On Mon, 16 Jan 2017, John R Pierce wrote:

the above isn't valid postgresql, as field names need to be in "doublequotes" 
not 'singlequotes'.


John,

  I thought I had changed them from single to double, yet missed seeing that
I did not do so. Mea culpa! I know better than that.

  That fixed all but one error which I'll address.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.6.1: INSERT with PK as serial

[Rich Shepard]

On Mon, 16 Jan 2017, Tom Lane wrote:


It looks like something deleted the quote marks.  How are you entering
this SQL command, exactly?


Tom,

  I noticed that, too. Here's the first line:

INSERT INTO companies
('comp_id','comp_name','addr1','addr2','city','comp_state','postcode','country','phone','fax','e_mail','url','industry','status','comment')
VALUES
(1,'AG Spray Inc.',' ','PO Box
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','i...@agsprayinc.com','','Chemicals','Opportunity',''),

  Other than the comp_id PK column each column's data type is a delimited
string:

CREATE TABLE companies (
  comp_id serial PRIMARY KEY,
  comp_name varchar(64) NOT NULL,
  addr1 varchar(64),
  addr2 varchar(64),
  city varchar(16),
  comp_state char(2),
  postcode varchar(9),
  country varchar(12) DEFAULT 'USA' NOT NULL,
  phone varchar(10),
  fax varchar(10),
  e_mail varchar(64),
  url varchar(64),
  industry varchar(24) NOT NULL
CONSTRAINT invalid_industry
CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
'Ports/Marine Services', 'Transportation')),
  status varchar(20) NOT NULL
CONSTRAINT invalid_status
CHECK (status in ('Client', 'Proposal submitted', 'Prospect',
'Referral', 'Opportunity', 'No further contact')),
  comment text 
);



Also, you can't just write double commas to leave out a item in the
value list.  You could write DEFAULT there, ie


  I tried with ,, and ,'', and ,' '. Same error each time. Also, listing
each column name does not fix the problem.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 9.6.1: INSERT with PK as serial

[Rich Shepard]

On Tue, 17 Jan 2017, David Rowley wrote:


You may have more luck getting an answer if you include the statement
causing the problem.


David,

  Here's an example:

INSERT INTO companies VALUES
  (1,'AG Spray Inc.',,'PO Box 
12129','Salem','OR','97309-0129','USA','503-371-7907','888-273-0937','i...@agsprayinc.com',,'Chemicals','Opportunity'),

and the associated error message:

psql:companies.sql:1: ERROR:  syntax error at or near "Spray"
LINE 1: INSERT INTO companies VALUES (AG Spray Inc.,,PO Box 12129,Sa...


If you're not specifying the column names, then the VALUES list must
have the same number of values, in the same order as the columns
defined on the table.


  As I wrote in the original message, I did this.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 9.6.1: INSERT with PK as serial

[Rich Shepard]

  I have a table with the primary key type of serial. The initial data load
has 40 lines, and each has a value for every column including sequential
integers from 1 through 40 for the data. When I try to read it in using
'psql -d  -f ' I get a syntax error at the
second word in the string that is column 2. The error message does not print
the integer PK. If it matters, I have all 14 columns specified but not
explicitly named in the INSERT INTO command.

  I've looked in the manual without learning how to INSERT data in a table
with a serial PK and need to learn how to do this.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Default column value [ANSWERED]

[Rich Shepard]

On Fri, 30 Dec 2016, David G. Johnston wrote:


"The CHECK clause specifies an expression producing a Boolean result which
new or updated rows must satisfy for an insert or update operation to
succeed. Expressions evaluating to TRUE or UNKNOWN succeed."

NULL == "UNKNOWN"


David,

  I forgot about that. Thanks for pointing it out to me.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Default column value

[Rich Shepard]

On Fri, 30 Dec 2016, Adrian Klaver wrote:


DEFAULT is what is the column is set to if the user does not specify a
value. As shown above a user can supply a NULL value. To guard against
that the NOT NULL constraint is required.


  One more case I'd appreciate being clarified: when the column's value has
a check constraint with acceptable values in a list. For example,

param_units VARCHAR(8) DEFAULT 'mg/L'
CONSTRAINT param_units
  CHECK (param_units IN ('ppm', 'mg/L', 'ug/L', 'umho/cm', 'percent', 
'cfm', 'gpm')),

  Seems to me that if values are constrained by a list a NULL cannot be
entered by the user. Is this correct?

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Default column value

[Rich Shepard]

On Fri, 30 Dec 2016, Adrian Klaver wrote:


DEFAULT is what is the column is set to if the user does not specify a
value. As shown above a user can supply a NULL value. To guard against
that the NOT NULL constraint is required.


  Thanks, Adrian. This was not clear to me when I read the manual.

Happy New Year,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Default column value

[Rich Shepard]

On Fri, 30 Dec 2016, Tom Lane wrote:


No, because you can explicitly insert a null. DEFAULT only controls what
happens when you omit the column in an INSERT command.


tom,

  Thanks for clarifying. I did not pick this up from reading the manual and
knew that NULL could be an explicitly-defined default value.

Much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Default column value

[Rich Shepard]

  Reading the 9.6 docs suggests an answer to my question, but does not
explicitly answer it, so I ask here.

  If a column has a default value specified does this mean the column cannot
contain a NULL value? In other words, is DEFAULT  NOT NULL
redundant?

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Book or other resource on Postgres-local code?

[Rich Shepard]

On Thu, 29 Dec 2016, Guyren Howe wrote:


I would like to find a book or other resource about SQL server-side
programming (stored procedures etc) best practices in general and for
Postgres in particular.


  Start here:


Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Rich Shepard]

On Thu, 29 Dec 2016, Nicolas Paris wrote:


Hi I'd like to tell about Sql Power Architect


Nicholas,

  SPA was going to be my next re-examination after dbeaver. Since the latter
easily accomplished what I needed I stopped there.

Thanks for the reminder,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Rich Shepard]

On Wed, 28 Dec 2016, Adrian Klaver wrote:


An example from my machine that works:
aklaver@tito:~/bin> java -jar schemaSpy_5.0.0.jar -t pgsql -s public -u 
postgres  -db production -host localhost  -dp 
/home/aklaver/bin/postgresql-9.4.1212.jre6.jar -o s_spy


Adrian,

  That's interesting. I specified my username, not postgres, since that's
how I access the databases from the psql CLI. But, since I've resolved the
issue to my satisfaction and deleted the schemaSpy subdirectory I've no
incentive to try again.

  Time now to fill them thar tables with fake data so I can test as I
develop.

Happy new year to you,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Er Data Modeller for PostgreSQL

[Rich Shepard]

On Fri, 23 Dec 2016, Thomas Kellerer wrote:


Scott Mead just blogged about using SchemaSpy with Postgres


Thomas,

  I've spent the past two days without success trying to get schemaSpy running 
here
and have found a solution that works quickly and easily: dbeaver
. Yes, it's a GUI rather than CLI application but
it works ... and it's F/OSS under the GPL. Best of all worlds.

  I downloaded the postgresql-jdbc driver and installed it in a couple of
places trying to make schemaSpy happy, and futzed with pg_hba.conf access
control. The dbeaver application downloaded and internally installed the
postgresql-jdbc driver it wants to use.

  It took no time to figure out how to create, test, and establish a
connection to the databases and examine their E-R diagrams. They're now
saved as .png files.

Thanks very much for your pointer and my wishes for a healthy, happy, and
prosperous 2017 to you and all who responded to my question,

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Securing Information

[Rich Shepard]

On Tue, 27 Dec 2016, Chris Weekes wrote:


I was wondering what steps if any need to be taken to ensure that the
patient and operational data is secure on a machine and or across the
network.


Chris,

  I'm far from an expert but until more knowledgeable folks respond I'll
offer a couple of quick suggestins. First, in your postgres data/ directory
modify pg_hba.conf. Specifying hostssl and an appropriate authentication method
will help. Second, partition your users into postgres role specifying what
each role can access and do.

  The postgresql-9.6-US.pdf manual has all the details.

HTH,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Wed, 28 Dec 2016, Martijn Tonies (Upscene Productions) wrote:


Not open source, but also not pricey (IMO): Advanced Data Generator.
http://www.upscene.com/advanced_data_generator/

Generates e-mail addresses, street names, first & last names, company names,
complex relationships etc.

And yes, this is our product. ;)


Martijn,

  Thank you for making me aware of your company and product. However, after
20 years of using only F/OSS to run my business (and personal computing)
needs and contributing to several open source projects along the way my
preference is to use such tools. When I get the large database application
up and running I'll post it on github and turn it loose into the F/OSS world
under the GPL.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Tue, 27 Dec 2016, Adrian Klaver wrote:


As it happens there is a Python version of the a fore mentioned faker:
https://pypi.python.org/pypi/Faker/0.7.7


Adrian,

  Impressive and complete. It will generate all the data I need.

Many thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Tue, 27 Dec 2016, Adrian Klaver wrote:


As it happens there is a Python version of the a fore mentioned faker:
https://pypi.python.org/pypi/Faker/0.7.7
It was I use to generate fake/sample data.


Adrian,

  Aha! That's a great start for me.

Many thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Tue, 27 Dec 2016, Adrian Klaver wrote:


What sort of data do you want to create?


Adrian,

  Various text, date, and numeric values.


If it is data specific to a field of study then things might get trickier.


  It's not a common database. I'll probably need to cobble together generic
data of the appropriate types myself.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Tue, 27 Dec 2016, Steve Crawford wrote:


You could start here:
http://www.softwaretestingmagazine.com/tools/open-source-test-data-generators/



I have rolled my own on occasion by just pulling some public lists of most
common given names and family names and toing a full-join. Same for city,
streets, etc.


Steve,

  Thanks very much for the URL. One application is small (7 tables), the
other is three times that size (23 tables). If I need to find public domain
data on the Web, I'll do that.

Much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Generating sample data

[Rich Shepard]

On Tue, 27 Dec 2016, Greg Navis wrote:


In the Ruby land there's a gem called faker
 that allows you to generate fake data.
However, I'm not sure it can generate data based on a schema so a little
bit of scripting my be necessary. Would this approach work for you?


Greg,

  I work in Python, not Ruby, so this might be too big of a hurdle.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Generating sample data

[Rich Shepard]

  My previous databases used real client (or my own) data; now I want to
generate sample data for the tables in the two applications I'm developing.
My web search finds a bunch of pricey (IMO) commercial products.

  Are there any open source data generators that can provide sample data
based on each table's schema?

TIA,

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error in column constraint syntax

[Rich Shepard]

On Tue, 27 Dec 2016, David G. Johnston wrote:


​5.3.1 is instructional.  The SQL Command chapter is the
authoritative source for syntax.


David,

  I'll read that, too.


CHECK ( expression ) [ NO INHERIT ] |   -- you are missing the mandatory (
) surrounding your expression
All of the examples in 5.3.1 have a "(" following the word CHECK as well...


  Yes, I see now that I misplaced the opening parenthesis and missed the
second closing parenthesis at the end of the IN list.

  I thought I had tested this syntax; obviously I did not.

Thanks,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Error in column constraint syntax

[Rich Shepard]

  I'm encountering DDL syntax errors using postgres-9.6.1 that I've not
before seen, and I'm having a difficult time finding the cause when reading
the appropriate sections of the manual; Section 5.3.1 in this case:

CREATE TABLE Agencies (
  org_name VARCHAR(48) PRIMARY KEY,
  acronym VARCHAR(8) DEFAULT ' ',
  org_lvl VARCHAR(8) DEFAULT 'State'
 CONSTRAINT invalid_agency_level
 CHECK org_lvl IN ('Federal', 'State', 'County', 'City', 'Local', 
'Regional'),
  website VARCHAR(64) DEFAULT ' ',
  created_at TIMESTAMP,
  created_by TEXT NOT NULL,
  updated_at TIMESTAMP,
  updated_by TEXT NOT NULL,
  comment TEXT
);

  psql reports:

:86: ERROR:  syntax error at or near "org_lvl"
LINE 6:  CHECK org_lvl IN ('Federal', 'State', 'County', 'City',...
   ^
  Having a default value or a named constraint makes no difference, and the
maximum size of the org_lvl column is that of the longest string so I am not
seeing the source of my error.

  Where, other than Section 5.3.1 of the manual can I learn why there's an
error?

  I also have several errors of 'table not found' yet to be understood and 
resolved
but this one is at the top of the file.

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Syntax error needs fresh eyeballs

[Rich Shepard]

On Mon, 26 Dec 2016, Tom Lane wrote:


ERROR:  there is no unique constraint matching given keys for referenced
table "weather_params".


because Weather_Params.param isn't constrained to be unique.

 ...

Seeing that Weather_Data also has a site_id column, I'm going to guess
that what you wanted to put in Weather_Data is a two-column FK:

FOREIGN KEY (site_id, param) REFERENCES Weather_Params (site_id, param)

That would match Weather_Params' pkey, so it's enough to identify a
unique row of Weather_Params.


  Thanks, Tom. Yes, I do want a 2-column FK.

Much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Syntax error needs fresh eyeballs

[Rich Shepard]

  My schema includes three tables and psql throws an error I'm not seeing
when I try to read the schema into the database. I'd appreciate fresh eyes
looking at the table and learning what error I'm not seeing.

  The tables:

CREATE TABLE Weather (
  site_id INTEGER PRIMARY KEY,
  site_name TEXT,
  site_location TEXT
);


CREATE TABLE Weather_Params (
  site_id INTEGER
REFERENCES Weather(site_id),
  param TEXT,
  param_unit TEXT,
  freq INTEGER,
  freq_unit TEXT,
  equip TEXT,
  PRIMARY KEY (site_id, param)
);


CREATE TABLE Weather_Data (
  site_id INTEGER
REFERENCES Weather(site_id),
  monit_date DATE,
  monit_time TIME,
  read_by TEXT,  -- name of employee collecting data
  param TEXT
REFERENCES Weather_Params(param),
  param_value REAL,
  PRIMARY KEY (site_id, monit_date, monit_time, param)
);

  The error:

ERROR:  there is no unique constraint matching given keys for referenced
table "weather_params".

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CRM where pg is a first class citizen?

[Rich Shepard]

On Tue, 13 Dec 2016, Adrian Klaver wrote:


This killed the community(Open Source) edition going forward:
https://community.sugarcrm.com/thread/18434


  I'd like to comment regarding this paragraph from the above-referenced
blog post:

"In the course of the past five years, we have surveyed tens of thousands of
Sugar Community Edition users and found that we see two types of users of
Sugar Community Edition: 1) developers that wish to build on an open source
CRM platform, and 2) users, generally first time CRM users, that are looking
for a free/inexpensive CRM solution. We don’t believe that the current Sugar
Community Edition serves both audiences effectively. We envision an open
source solution targeted exclusively for developers. And, we also envision a
simpler way for first-time CRM users to find and use CRM."

  This is an interesting perspective, but not surprising for a large
for-profit corporation like SugarCRM.

  I'm an environmental consultant sole practitioner and have been looking
for years for a postgres-supporting CRM that I could use. There is none.
Every business is different and has different needs. This is why a generic
CRM like Sugar that tries to fit every business regardless of type or size
forces its customers to fit into their generic model rather than supporting
a developer _and_ end-user framework that can be customized for each
business's specific needs and way of working.

  That's why I'm developing my own using PyQt5, Python3, psychpg2, and
postgres-9.6.

  The django example I mentioned in an earlier post is written for software
companies; so is a PHP-based one (XRMS) that was abandoned a decade ago.
Product providers are different from service providers and small companies
(or solo practitioners). Neither fit my needs.

  I have the postgres schema that works for me and am willing to share it
with others because of this thread. I had not planned on putting it on
GitHub, but see no reason not to do so if there's interest by others. I'm
starting to learn PyQt5 and Python3 after a decade of wxPython use with
Python2 and am just about ready to start creating the UI.

Rich







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CRM where pg is a first class citizen?

[Rich Shepard]

On Tue, 13 Dec 2016, John R Pierce wrote:


CRM (Customer Relationship Manager) != CMS (Content Management System).


John,

  True, and Django can be used for a CRM; there's an example by a North
Carolina shop. Search the web for "django crm". I was going to use django
but decided to make my CRM a stand-alone application.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Looking for an online mentor

[Rich Shepard]

On Thu, 8 Dec 2016, Metare Solve wrote:


Will Python enable me to do the same things that I do with that kind of
big data processing program?


  Yes.


I need SOMETHING to use for analysis for the tests I'm going to have to
take when I job hunt so I'm exploring KNIME right now and doing the data
manipulation into what I'd do in access for analysis. I know, I need stats
too. You were educated as a biologist? I was educated as a pianist,
writer, and historian, lol. I have a lot to learn.


  While R is a great statistical and data analytical language (and can do
general programming, too), you'd be better off learning Python first and
looking beyond that only when you hit a dead end.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Looking for an online mentor

[Rich Shepard]

On Thu, 8 Dec 2016, Adrian Klaver wrote:


GUI's only get you so far. At some point you will need to dive deeper to
get what you. I am mostly a self taught programmer(biologist by training)
so I understand the hill you are facing. The language I use is Python,
mainly because to me it made sense. For you it might be a good choice as
it is quite prevalent in the data analysis world. There are a lot of
places to turn to to learn how to use it. My suggestion for seeing if it
is something you can use would be to start here:


+1 for Python


Go through at least the Introduction to Python part. The rest has to do
with Django, Web framework built using Python.


  Mike Driscoll has a blog (I don't recall the URL) and his Python 101 is a
very good introduction. There are also a lot of online tutorials.

  I would suggest starting by learning a general programming language
(specifically Python). That puts you in a learnable mindset. SQL is a
set-oriented language and is quite different from procedural, object
oriented, and functional languages.

  You can learn on your own, and help is readily available on various
maillists. I'm an ecologist who learned FORTRAN (mostly self-taught) in grad
school, C on my own, and then moved to Python on my own. If you're
interested in data analysis Python's the way to go. Your description of what
you want to accomplish can be achieved using three components: postgres (and
SQL as the language it uses), Python as the glue between the database back
end and the user interface, and a widget set (PyQt5 is a good one) for the
UI.

  If you want to develop a web-based application then replace the UI with
django.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Thu, 8 Dec 2016, Chris Travers wrote:


Assuming relatively small files, bytea makes much more sense than a large
object.


Hi Chris,

  Most of the documents are only a few pages in size.


LOBs work best when you need a streaming interface (seek and friends) while
bytea's are otherwise much more pleasant to work with.


  They're not referenced frequently, only occasionally.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Thu, 8 Dec 2016, Adrian Klaver wrote:


http://initd.org/psycopg/docs/usage.html?highlight=binary#adapt-binary


  Thanks again, Adrian.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Thu, 8 Dec 2016, John DeSoi wrote:


I have been storing PDFs in Postgres for several years without any
problems. Documents range in size from a few pages to 100+ pages. I'm
using a bytea column, not large objects. I store the documents in a
separate database from the rest of the application data in order to make
it easy to exclude in database dumps or backup in some other way. I'm
currently managing about 600,000 documents.


John,

  This is really good information. Rather than using a separate database I
think that storing all PDFs in a separate table makes sense for my
application. Backup practices will be the domain of those using the
application (which I've decided to open-source and give away because I'm not
in the software business). A simple join to the appropriate data table will
make them available.

  Not having used the bytea data type before I'll read how to work with it.

Thanks very much for your insights,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] When to use COMMENT vs --

[Rich Shepard]

On Thu, 8 Dec 2016, Charles Clavadetscher wrote:


IMHO the fact mentioned by Chris Travers that comments on objects included
in the database can be used by tools to generate the documentation is
probably the most important (besides their being persisted).


Charles,

  Chris' contribution was the only one to answer my question.


As a matter of fact we have integrated this feature to extract comments to
generate the DB documentation in our internal MediaWiki based wiki.


  I'm not sure this is applicable to me. One project is for my business use,
the other for clients who will see only the entire application.

Thanks for explaining,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] When to use COMMENT vs --

[Rich Shepard]

  I have used '-- ' to enter comments about tables or columns and am curious
about the value of storing comments in tables using the COMMENT key word.
When is the latter more appropriate than the former?

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Tue, 6 Dec 2016, John R Pierce wrote:


indeed BYTEA is postgres's type for storing arbitrary binary objects that
are called BLOB in certain other databases.


John,

  I thought so.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Tue, 6 Dec 2016, David Wall wrote:


The advantages of storing in the database is that a DB backup will have
everything, instead of a DB backup and a file system backup. Using a BLOB,
you can certainly keep track of variable length PDFs.


David,

  I did not realize that a BLOB is not the same as a bytea (page 217 of the
9.6 PDF manual), and I cannot find BLOB as a postgres data type. Please
point me in the right direction to learn how to store PDFs as BLOBs.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PDF files: to store in database or not

[Rich Shepard]

On Tue, 6 Dec 2016, Joshua D. Drake wrote:


Due to the widely variable size of a PDF document, I would say no. I would
store the metadata and file location.


Joshua,

  I read your answer as "don't store them in the database, but store the
location in a column."

Thanks for confirming,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PDF files: to store in database or not

[Rich Shepard]

  With no experience of storing binary data in a bytea column I don't know
when its use is appropriate. I suspect that for an application I'm
developing it would be better to store row-related documents outside the
database, and want to learn if that is the appropriate approach.

  Consider an application that manages a fleet of vehicles. There's a
Vehicles table with information on each one (perhaps make, model, VIN, year
of purchase) and a Services table. There are many PDF documents associated
with each row in the tables: purchase contract, insurance form, service and
maintenance records, etc.

  My thinking is to not store these documents in the database, but to store
them in subdirectories outside the database.

  Your thoughts?

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Martin Collins wrote:


   The problem with kexi is that it does not run on Microsoft OSes which is
what my clients use.


There is a Windows package in the works, apparently. Version 3 already
builds on Windows.


Martin,

  However, it is highly unlikely that my clients (all large industrial
companies) would install anything other than what they buy from Microsoft.
Unfortunate, but true.


Flask has a simple SQLAlchemy wrapper now. It is also based on bootstrap
though I don't know how the widget set compares with Phoenix,


  Based on advice from someone who builds postgres applications for a living
I decided that for my purposes as a non-professional application developer
the most parsimonious approach is to simplify to postgres (model),
wxPython/Phoenix-3.0.2.0 (view), and Python3 (controller) with SQL embedded
in the appropirate methods. This will result in a stand-alone application
that can be packaged for easy installation and use by clients.

Thanks very much for your suggestions,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Mon, 5 Dec 2016, Johann Spies wrote:


Web2py is another (and maybe simpler) alternative to Django.


Johann,

  Thank you. I'll look at it.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, John McKown wrote:


I'm not really sure what ".. make it an application that my clients can
use. ..." really means.


John,

  It has to be simple and useful to naive users.


I guess it means that you have some code for an application (which uses
PostgreSQL as it's data repository), but it is difficult for many of your
users to use easily. I also don't know how much effort you want to put
into this. Would using C++ be acceptable? If so, then perhaps you should
look at QT from TrollTech. This started out as a cross platform (UNIX,
Windows, MAC) windowing system which has really grown. https://www.qt.io/
is a nice site where you can get started. But you would need a commercial
license if your software is not licensed as "open source".


  I'm familiar with Qt.


A possible alternative to QT is GTK+ (https://www.gtk.org/)​. It is
both GPL & LGPL licensed, so you can freely use it in commercial software.


  I migrated from C to Python a number of years ago so I've looked at
Python-based web frameworks (flask, django). I have developed a number of
applications in Python2 using wxPython; writing the UI takes most of the
time. But, I'll look again at the Python3 version called Phoenix and Peewee
(a lighter ORM than SQLAlchemy).


Sorry if I went off into left field on this.


  That's fine. All ideas are always welcome.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Martin Collins wrote:


Not tried it myself but have you seen
https://www.kde.org/applications/office/kexi/

And if that's no good http://flask.pocoo.org/ is simpler than Django.


Martin,

  The problem with kexi is that it does not run on Microsoft OSes which is
what my clients use. Long ago I looked at flask; that might be worth a
second look. I'm also seriously looking at peewee, which is an ORM simpler
than SQLAlchemy, and the Phoenix toolkit for the UI.

Thanks very much,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Adrian Klaver wrote:

The important questions that forgot to ask before, is how do you plan to 
deploy this:



Or are you looking to deploy a front end/database combination to each client?


  This way. I've no interest or expertise in maintaining a web site (my ISP
does this for mine). I would provide some guidance on installing necessary
software, but it's theirs to use as they choose.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Adrian Klaver wrote:


That is why I ended up learning Django, I could not find a GUI that was
cross platform and had the capabilities I needed. Others might have
suggestions. It would help though if you could list what you want in a GUI
platform:


  OK. I spent a lot of time over the past few years looking for a simpler
solution and they just don't exist.

  I'll learn django and take it step-by-step.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

On Sun, 4 Dec 2016, Adrian Klaver wrote:


Base is basically a GUI over the database.


Adrian,

  That's what I assumed it to be.


That being said my experience with Base is that is not up to the task. It
worked for me early on, but development on Base lagged relative to the
other modules in LO. My recent attempts have been less successful. Exact
examples on why, will have to wait on me dredging up the memories.


  I'm very open to suggestions what to use for the UI. I'm not a
professional coder and learning SQLAlchemy or Django takes too much time
away from my business.

  My clients all run Windows about which I know nothing. Is there a GUI for
postgres that works on all platforms and suitable for someone like me?

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres and LibreOffice's 'Base'

[Rich Shepard]

   I have postgres tables (with data) for a specific application but have
not found the time to learn django to make it an application that my clients
can use. It occurs to me that the most parsimonious approach is to use
LibreOffice's Base for the UI with postgres as the dbms-engine. While I work
in only linux (and unixODBC is installed), my clients all use various
flavors of Windows, but the three critical software applications (LO,
PostgreSQL, and ODBC) are available for Microsoft, too.

   I've scanned the Base portion of the LO User Guide and it looks to be a
practical solution to quickly providing clients with working database
applications.

   As this is completely new territory for me I'd like is to learn from
those who've done this before. As examples, What can/should I do as stored
procedures using PL/pgSQL? Can queries be created and tested using psql
before being imported into Base?

   All tips and gotcha's for a new user are certainly welcome.

TIA,

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invoice Table Design

[Rich Shepard]

On Tue, 29 Nov 2016, bto...@computer.org wrote:


The other bit of experience I'll share is the suggestion that invoicing is
a situation that lends itself to the uniformly incremented sequence
pattern. Accountants and comptrollers love this.


  Reading your message brought to mind a suggestion for Rob: look at the
source code for ledger-123 . It's a fork of
SQL-Ledger which I've used for my business for almost 20 years. It has a
functional invoicing capability and should give you ideas on how to
structure your database and tables.

  I know you'll need customer, invoice, line-item tables at the minimum. But
since I use it only to generate service invoices and post them to accounts
payable I don't know the details of how it works. I do use postgres
(currently -9.6.1) and httpd with it.

HTH,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Invoice Table Design

[Rich Shepard]

On Thu, 24 Nov 2016, Robert Heinen wrote:


A quick intro -- I'm helping a company switch from a mongo database over
to postgresql (yay!). The company is a marketplace app for musicians and
hosts. The basic idea is that a host can book a musician for an event,
like a wedding or a birthday. Also, an artist and a host can be either
basic or "pro" accounts -- if they're "pro" then they pay a little bit
more and get some extra features.


Bob,

  I suggest you go back to first principles of database design. Start with a
list of all the information needed by the company, artists, audiences, etc.
Just list every item, regardless of type or to whom it belongs. Forget the
original database as it will only continue to confuse you.

  One you have listed every item of information, start normalizing by
collecting similar items into a group. As examples, account type (pro,
amateur); musicians (name, account_type, contact_information), hosts (name,
perferred_music_type, contact_information); musician_rates (many-to-many
table of musician name, account_types, and rates); etc. You can find good
references in books or on-line on how to normalize data. Look at Joe Celko's
books; they're very helpful.

  Only you and your client know just what's needed. This approach will open
your client to data/information they need that is not provided by their
current software and will provide you the basis for designing the postgres
schemas.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, Adrian Klaver wrote:


To add to my previous post. If you do decide to follow the pg_upgrade
procedure in the README do a pg_dump of the 9.5 data just before you do
pg_ugrade and store it away in a safe place. The first time through a new
process does not always end well:)


Adrian, et al.:

  Did a pg_dumpall and stored it with my database-backups. Now that I know
where to find the bin/ directories pg_upgrade ran flawlessly. Quick and
easy.

Thanks all,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Wed, 16 Nov 2016, Alban Hertroys wrote:


pg_upgrade migrates your databases from your old (9.5) cluster to the
new (9.6) one. Initdb doesn't do that.


Alban,

  That's what I assumed to be the case.


If your 9.6 database does indeed contain your databases, then something
must have done the pg_upgrade for you. Perhaps the slackware package
script does something like that,


  No, it only builds the package. I manually install it and run initdb.


What I think what happened is that you are using the new pg 9.6 psql
binary to list the databases in your old 9.5 cluster.


  Not only list them, but access them from the command line. This is what
I'm trying to understand.

  Regardless, I'll stop the running postgres, run pg_upgrade, then start it
from the /9.6/data/ directory.

Thanks,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, Rich Shepard wrote:


$ /usr/bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.6.1

ls -al /usr/bin/pg_ctl
lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl ->
../lib/postgresql/9.6/bin/pg_ctl*


  To increase my understanding I want to resolve an apparent discrepancy in
versions.

  After installing and initiating 9.6.1 that's reported as the current
running version, yet I've not yet migrated the cluster from 9.5.4 to 9.6.1.

  Last week, after a kernel upgrade, I rebooted the system and re-started
postgres pointing to the 9.5/data/ directory. When I enter the command
'psql -l' I see all the databases and assumed they running on 9.5.4. But
that's not the case, is it?

  If 9.6.1 is currently running after running initdb, and I can access my
databases, what does pg_upgrade do that's necessary?

A curious mind wants to learn,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, Adrian Klaver wrote:


So what do you get with
/usr/bin/pg_ctl --version


  Interesting:

$ /usr/bin/pg_ctl --version
pg_ctl (PostgreSQL) 9.6.1


and given John's suggestion:
ls -al /usr/bin/pg_ctl


ls -al /usr/bin/pg_ctl
lrwxrwxrwx 1 root root 32 Nov 15 14:16 /usr/bin/pg_ctl ->
../lib/postgresql/9.6/bin/pg_ctl*

   Aha! That's where the bin/ directories are. There's a 9.3.4/ and 9.5/
subdirectory there, too.

  Will do a pg_dumpall and then run pg_upgrade.

Thanks, both of you. I learned valuable lessons,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, Adrian Klaver wrote:

Rich is using Slackware and I am pretty sure it marches to a different 
drummer.


Adrian,

  And a different band. Pat likes MariaDB which replaced MySQL so PostgreSQL
is not part of the base distribution. But, SlackBuilds.org provides a build
script that's worked for me since some early version 7 of postgres.

Regards,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, Adrian Klaver wrote:


Assuming the bindirs are in your $PATH:

aklaver@panda:~> whereis -f pg_ctl


Adrian,

  In my case:

$ whereis -f pg_ctl
pg_ctl: /bin/pg_ctl /usr/bin/pg_ctl /usr/X11R6/bin/pg_ctl
/usr/bin/X11/pg_ctl /usr/X11/bin/pg_ctl /usr/man/man1/pg_ctl.1
/usr/man/man1/pg_ctl.1.gz /usr/share/man/man1/pg_ctl.1
/usr/share/man/man1/pg_ctl.1.gz /usr/X11/man/man1/pg_ctl.1
/usr/X11/man/man1/pg_ctl.1.gz


Even if only one is the $PATH:



you can usually figure out where the other is.


  I suppose if I knew which version is in each of those directories it would
make life easier.

  Currently, only 9.5.4 is running; 9.6.1 is installed and initiated but not
running. When I invoke 'psql -l' I see the clusters installed in 9.5.4.

  Will use pg_dumpall and pg_restore ... tomorrow morning.

Thanks very much,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

On Tue, 15 Nov 2016, John R Pierce wrote:


9.5 is considered a major version, 9.5.4 is a minor.this will change
when 10 is released, from 10 on, major versions will be 10, 11, 12, ...


John,

  So the upgrade from 9.5.4 to 9.6.1 is as I assumed: major.


based on the data paths you gave, I'm guessing you're on a
redhat/centos/fedora type distribution? those put the binaries in
/usr/pgsql-X.Y/bin for version X.Y


  Actually, slackware. No /usr/pgsql-x/ at all. Slackware binaries (and
packages such as postgres provided by SlackBuilds.org are all installed in
/bin/. There are no /bin directories associated with postgres.

  So, I'll go the pg_dumpall route then.

Thanks very much,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrade from 9.5.4 to 9.6.1

[Rich Shepard]

  All my previous version upgrades were performed by running pg_dumpall in
the older version followed by running 'pgsql -f ...' to install the dumped
.sql file, usually because the upgrade jumped several versions. Now I'd like
to try the available postgres commands.

  The older version is installed in /var/lib/pgsql/9.5/data and I just
initiated the new version in /var/lib/pgsql/9.6/data. The earlier version is
currently running.

  Is pg_upgrade the recommended way to upgrade from one minor version to the
next? The 9.5 manual recommends this approach for _major_ upgrades (e.g.,
8.4.7 to 9.6.1), but not for _minor_ upgrades (e.g., 9.0.1 to 9.0.4). That's
a first digit upgrade and a third digit upgrade. Since 9.5.4 to 9.6.1 is a
second digit upgrade I suppose it's semi-major, but in which upgrade camp
does it belong?

  The command is:

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

and I don't know where to find -b and -B. On my Slackware-14.1 server I have
/usr/bin/postgres and assume it is for the 9.5 release since that's running
and the 9.6 release is initiated but not invoked. The data directories are
easy but where do I look for the two bindirs?

TIA,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] E-R diagram code U1

[Rich Shepard]

On Sat, 29 Oct 2016, Adrian Klaver wrote:


http://stackoverflow.com/questions/5628533/what-does-i1-i2-u1-means-in-visio-database-relationship-diagram
http://stackoverflow.com/questions/23533702/about-entity-relationship-diagram


  Thanks, Adrian. I suspected that I was not effectively expressing my
search terms.

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] E-R diagram code U1

[Rich Shepard]

On Sat, 29 Oct 2016, Tom Lane wrote:


   What does U1 represent?


Unique constraint, perhaps?  I'm just guessing.


Tom,

  That's my guess, too. I've not seen it used before now and did not know if
it's a standard code for something or one specific to this agency.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] E-R diagram code U1

[Rich Shepard]

  Looking at the entity-relationship diagram for a regulatory agency database
they have attribute identifiers of PK, FKn, and U1. The first is the Primary
Key and the second is a Foreign Key, but I've not before seen a 'Un'
identifyier. I've no idea what DBMS they're using (but suspect Oracle). My
web search used in-effective terms so I could not find the answer there.

  What does U1 represent?

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Save query results to new table [RESOLVED]

[Rich Shepard]

On Thu, 27 Oct 2016, John R Pierce wrote:


CREATE TABLE newtable AS SELECT ...
https://www.postgresql.org/docs/9.5/static/sql-createtableas.html


  Thank you, John.

Much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Save query results to new table

[Rich Shepard]

  It's been several years since I worked with postgres so I tried searching
the web for the correct syntax to save query results to a new table, but
without results.

  I'm running postgresql-9.5.4 here. The query extracts 28162 rows from a
table of 122365 rows. I thought 'save table as' would do the job but have
not found the correct syntax to do this.

  My query is:

select * from monitor_sites where param = 'Cd' or param = 'Co' or param = 'As' 
or param = 'Hg' or param = 'Zn' or param = 'Pb' or param = 'Cr' or
param = 'Cu';

  If you point me to the appropriate page in the 9.5.x manual I'll figure it
out.

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] System crashed: fatal error restarting postgres

[Rich Shepard]

On Fri, 30 Sep 2016, Tom Lane wrote:


Wrong permissions on /dev/shm, perhaps?


Tom,

  Yes. I keep forgetting about this since I don't reboot this
server/workstation often.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] System crashed: fatal error restarting postgres [FIXED]

[Rich Shepard]

On Fri, 30 Sep 2016, Adrian Klaver wrote:


See here:
https://www.postgresql.org/message-id/24208.1473724630%40sss.pgh.pa.us


  Ah, shoot! I completely forgot about this. Yes, I reset the perms on
/dev/shm and that fixed the problem.

My apologies to all,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] System crashed: fatal error restarting postgres

[Rich Shepard]

  A java application threw a major error when closing and it crashed my
system (including wiping out most of /tmp). After rebooting I su'd to user
postgres and tried to start postgresql-9.5.4, but this error displays:

postgres@salmo:~$ FATAL:  could not open shared memory segment
"/PostgreSQL.1804289383": Permission denied

  Please advise me on how I proceed to clear this and restart the
application.

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server crashed, now cannot start postgres [FIXED]

[Rich Shepard]

On Mon, 12 Sep 2016, Tom Lane wrote:


Hmm, AFAIK that's what it should be out of the box. Certainly on my RHEL6
machine I see


  Until I make the time to upgrade this host from Slackware-14.1 to -14.2
the kernel is 3.10.17-smp.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server crashed, now cannot start postgres [FIXED]

[Rich Shepard]

On Mon, 12 Sep 2016, Tom Lane wrote:


A look at the code suggests this is shm_open() returning EACCES. Not sure
why that's happening. If this is a Linux box, maybe the permissions on
/dev/shm are bollixed?


Tom,

  Yes, it's a linux box. And /dev/shm/ does have incorrect permissions
(755). Thanks to your response I remembered that chromium does not run until
I follow its advice to chmod 1777 /dev/shm. Sure enough, chromium would not
load.

  So, I changed the perms on /dev/shm/ and now both postgres and chromium
work.

Very much appreciated,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Server crashed, now cannot start postgres

[Rich Shepard]

  Tried to compile 3 large programs at one time and the CPU overheated,
shutting down the server. Now when I try to start postgres-9.5.4 (as the
superuser, postgres) I get this result:

postgres@salmo:~$ postgres -D /var/lib/pgsql/9.5/data/ &
[1] 14544
postgres@salmo:~$ FATAL:  could not open shared memory segment
"/PostgreSQL.1804289383": Permission denied

  Not before encountering this problem I've no idea where to look to see how
to fix it. Please advise.

TIA,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

[Rich Shepard]

On Wed, 7 Sep 2016, Patrick B wrote:


2 - I've never done a Postgres upgrade before, can you give some guide
here? Is usually a easy thing? How long can it take? Is a downtime needed?


Patrick,

  I suggest the place to start is 'man pg_upgrade.' It is helpful.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Another thing that came to mind is compatibility with existing
applications/clients. You say you have been running using trust and I am
betting your client connection parameters reflect that. Now for
connections methods that can 'see' the .pgpass file and use libpq as the
their underlying Postgres library then things should work. Otherwise your
applications may not be able to connect until you supply the correct
password in some manner.


  That's a concern. My business financial software uses postgres as the
backend and a browser UI and I enter my username and password on the login
page. It works with auth method trust. I've no idea exactly how it connects
to the database. Since it ain't broke I won't futz with it and possibly
break it. I've other things with higher priorities.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:

Well first, if you are going to use trust as your auth method then specifying 
a password is moot exercise.


  I tried adding an explicit password to ~/.pgpass with md5 as the auth
method, but that didn't work so I went back to trust. That's served well for
19 years. :-)


Second, not sure where you are in the process, but any time you change the
pg_hba.conf file you will need to give Postgres a reload signal to get it
to recognize the changes. Again not sure how you are signalling Postgres
but if you are using pg_ctl
https://www.postgresql.org/docs/9.5/static/app-pg-ctl.html
then something like:

pg_ctl reload  -D path_to_your_datadir

as OS user postgres.


  Good to know. I use pg_ctl stop and start with the path on both command
lines.


Third, .pgpass should hold information that already exists in the database
system tables. It is not a mechanism for entering that information into
the database. So yes, you will need to use ALTER ROLE to create the
password inside Postgres.


  OK. I'll try that for the learning experience.

  So much to learn and so little time ...

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


You don't it directly. That information is supplied by Postgres when you
do CREATE or ALTER ROLE. The -W switch just does that for the
superuser(postgres in your case) when you initdb a new cluster.


Adrian,

  OK. That makes sense.


Sorry, old habits. pg_user is a version of the pg_shadow view that blanks
out the actual password. pg_shadow is a view over the table pg_authid,
where the actual information is stored now. In any case, again they are
not tables/views you directly modify.


  Good. Then I won't spend time with them.

  So, given my single-user situation do you think that I should ALTER ROLE
to add my password? Adding it to ~/.pgpass did nothing positive when I
changed auth method to md5; my attempt to open a database failed because
that password was rejected. Strange ... to me.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


I would take Charles's suggestion and set up a .pgpass file just to be
safe.


  The file ~/.pgpass already exists, but without an explicit password. I
added my password (plain text). The file already had perms 0600. Perhaps my
password was rejected with the authorization method set to md5 was because
the password field was missing.

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


-W is not referring to the OS user but the database superuser. Now in your
case they have the same name, postgres. The settings in /etc/passwd are
not relevant to what -W is doing. -W is referring to user information
being stored in the cluster in the system tables pg_user and pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html


  I see the pg_user in Section 49.79 but don't know how to correctly
configure it. It seems that pg_shadow is unnecessary since I don't need to
provide compatibility with versions < 8.1.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Just be aware that you now have a password for the postgres user and that
if you ever do enable md5 you will need it. I would take Charles's
suggestion and set up a .pgpass file just to be safe.


Adrian,

 OK. I'll also read the page at the URL you provided and add a password for
me, too.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


-W is not referring to the OS user but the database superuser. Now in your
case they have the same name, postgres. The settings in /etc/passwd are
not relevant to what -W is doing. -W is referring to user information
being stored in the cluster in the system tables pg_user and pg_shadow:

https://www.postgresql.org/docs/9.5/static/catalogs.html


Adrian,

  I did not know this. Thanks for the information.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Well, there you have it. As Adrian suggested you may set temporarily the
authentication method to trust, set yourself a password and change it back
to md5.


Charles,

  I've had a password on this LAN for almost 2 decades. I've not before set
a password specifically for postgresql. Since I'm the only user and the
trust authentification has worked for the same period, I'll leve it that
way.

Thanks for your help,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall [FIXED]

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually there is an important difference. In your 9.3 file you have set
METHOD set to trust and in the 9.5 file it is set to md5, which is
password. Set the METHOD to trust in your 9.5 file and restart the
database. Now for the non-socket access methods this is a security risk,
so you will want to change it back at some point once you get the
users/passwords figured out


Adrian,

  I saw that but missed the meaning. I'm the only user on this system so
there is no internal security risk.

  After learning that the Slackware script's restart does not work[1] I
stopped the process then restarted it. I can now access my databases and the
financial software via the browser UI.

  Now I can remove the 9.3.4 directory and be comfortable that the next
upgrade will be smooth.

  Thanks very much for your patient help!

Much appreciated,

Rich

[1] I've seen this same behavior in other rc. scripts. Stopping
and starting works in all cases.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sat, 3 Sep 2016, Adrian Klaver wrote:

Or if you are fine running the 9.5 instance at port 5432, what happens if you 
do?:


psql -d postgres -U some_user -p 5432


$ psql -d postgres -U rshepard -p 5432
Password for user rshepard: 
FATAL:  password authentication failed for user "rshepard"

DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

but,

$ psql -d postgres -U postgres -p 5432
Password for user postgres: 
psql (9.5.4)

Type "help" for help.

postgres=#

  So, it appears to be a password issue. Please advise me how to get the
running postmaster process to accept my accessing databases without a
password being required.

Thanks again,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


But the message you sent me offlist showed the 9.5 instance running.


  But now it's not running.


How are you starting the instance?


  As superuser poostgres: pg_ctl start -D /var/lib/pgsql/data &

  After removing an orphaned postmaster.pid the above seemed to have started
postgres, but there's no postmaster process running.

Are you sure that the password being asked for is not for the OS user you are 
using to run whatever start script you are using?


  Thinking postmaster is running I tried this:

$ psql crm
Password: 
FATAL:  password authentication failed for user "rshepard"

DETAIL:  User "rshepard" has no password assigned.
Connection matched pg_hba.conf line 80: "local   all all
md5"
psql: FATAL:  password authentication failed for user "rshepard"

  The crm database is owned by me.


I have never used it, but I am pretty sure that is not what -W means. It
looks to me that it asks you to create a password at init for the database
superuser(in this case postgres) and only that user when that user tries
to use log into a database after the cluster is started.


  From man initdb:

-W, --pwprompt
   Makes initdb prompt for a password to give the database superuser.
   If you don't plan on using password authentication, this is not
   important. Otherwise you won't be able to use password
   authentication until you have a password set up.

  The superuser already exists in /etc/passwd.


Do you remember what password you specified?


  Yes. It's the same password I use for logging in as a user. If it's the
superuser password being requested, then that's the same as my user
password.

  The Slackware rc.postgresql file for 9.5 has changed from 9.3 and earlier.
It's asking for passwords:

 if [ ! -e $DATADIR/PG_VERSION ]; then
echo "You should initialize the PostgreSQL database
at location $DATADIR"
echo "e.g. su postgres -c \"initdb -D $DATADIR
--locale=en_US.UTF-8 -A md5 -W\""
exit 6
 fi

  Note the '-W' at the end. But, I ran initdb from the command line as user
postgres.

Rich




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:


Actually you already have. From an email that I just realized was offlist:

"As superuser postgres connected to 9.5.4 'psql -l' shows all databases
loaded:

xxx  | rshepard   | UTF8 | C   | en_US.UTF-8 |
xxx| rshepard   | UTF8 | C   | en_US.UTF-8 | rshepard=CT


  I did not recognize this as the answer to Charles' question.

Thanks,

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Are you able to connect to the new instance with any user at all, e.g.
with psql? If so you can use the command \du to list all users.


Charles,

  No. The postmaster is not running; trying to start it requires a password
which is also rejected.

  Since I have the pg_dumpall data in a large .sql file I think the best
solution is for me to remove the 9.5.4 package completely, re-install from
the package tarball, run initdb, then start the postmaster and restore all
the databases in the cluster.

  I'm thinking that without paying attention when I initially ran initdb I
used a commandline found on a web site (rather than just reading the man
page) and used the -W option which requires a password for everything. So
starting over from scratch _should_ remove all issues and successfully end
this thread.

Thanks,

Rich



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Adrian Klaver wrote:

The above is not possible. If the postmaster was not running there would be 
no rejection error.


Adrian,

  Yes. I conflated when the postmaster was running -- using the 9.3.4 server
-- and now when it's not running. Now I'm wondering if I mistakenly used the
-W option to initdb which requires password use for access.

Don't do that, you will end up right back at this point again. Find the 
pg_hba.conf files for your 9.5 cluster and your 9.3 cluster and post them 
here.


  OK. But when I used 'diff -y' I saw only a paragraph of comments as
different. I'll try attaching them; they're ~100 lines each. If the
attachments are stripped off my the mlm I'll list them within the message
body.

Rich


# PostgreSQL Client Authentication Configuration File
# ===
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local  DATABASE  USER  METHOD  [OPTIONS]
# host   DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostsslDATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# --
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser.  If you do not trust all your local users,
# use another authentication method.


# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
hostall all 127.0.0.1/32trust
# IPv6 local connections:
hostall all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# 

Re: [GENERAL] Upgrading using pg_dumpall

[Rich Shepard]

On Sun, 4 Sep 2016, Charles Clavadetscher wrote:


Does the user rshepard exist in the new 9.5 instance?


  I assume so but do not know how to check this.

Rich


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general