Thread: 2 questions

anj patnaik wrote:
> I've got 2 more questions. The cron job is now setup and email gets generated with proper body.
> 
> I've one setup with NFS which appears to work smoothly. Now soon, I will be given a Linux VM on a
> different physical server, but will retain my NFS mount. I've installed Postgres 9.4 using the
> graphical installer and specified the directory for the nfs mount.
> 
> 1) When I move to the new VM, can I keep using that NFS mount without having to do a re-install of PG?
> This would be a different physical machine.

I don't know what the graphical installer does.
If all you want to share is the PostgreSQL software, you can certainly use an NFS mount
for that as long as the architecture is the same on all machines.
But there may be other things necessary for running PostgreSQL, for example
startup scripts, that you may want to have everywhere.

> 2) I have a cron job that deletes log files older than 10 days, but I am noticing rather large log
> files. Is there a way to limit the size of log files?

There is the log_rotation_size parameter that limits the size of an individual
log file, but it won't reduce the overall amount of log.

> users do upserts and they are valid, but those are getting dumped as error statements. I set the
> verbosity to "terse", but still seeing lots of log output.
> 
> My settings are as follows:
[...]
>  log_min_error_statement     | error                          |

Change that to "fatal", and the error messages will no longer be in the log.

Yours,
Laurenz Albe

On 11/25/2015 10:28 AM, anj patnaik wrote:
> Hello all,
> I've got 2 more questions. The cron job is now setup and email gets
> generated with proper body.
>
> I've one setup with NFS which appears to work smoothly. Now soon, I will
> be given a Linux VM on a different physical server, but will retain my
> NFS mount. I've installed Postgres 9.4 using the graphical installer and
> specified the directory for the nfs mount.

Define 'setup with NFS'.

So are you saying this how you set up the old instance or how you are
setting up the new VM?

>
> 1) When I move to the new VM, can I keep using that NFS mount without
> having to do a re-install of PG? This would be a different physical
> machine.

Where is Postgres installed now?

Not entirely following, but I hope you are not asking if two Postgres
installs can share the same NFS mount? That will end badly.


It might help if you give a schematic description of what you are trying
to achieve.

>
> 2) I have a cron job that deletes log files older than 10 days, but I am
> noticing rather large log files. Is there a way to limit the size of log
> files?

What I do is keep the previous 2 days of files as written and then keep
compressed files older then that to some period of time. The files
compress a good bit so it works well for me. This is done via a cron
script that runs each night.

>
> users do upserts and they are valid, but those are getting dumped as
> error statements. I set the verbosity to "terse", but still seeing lots
> of log output.

So what are the errors?

As to 'terse':

http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
log_error_verbosity (enum)

     Controls the amount of detail written in the server log for each
message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE,
each adding more fields to displayed messages. TERSE excludes the
logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE
output includes the SQLSTATE error code (see also Appendix A) and the
source code file name, function name, and line number that generated the
error. Only superusers can change this setting.


You lose a lot of valuable information this way. I would go with Albe's
suggestion and change log_min_error_statement.


>
> My settings are as follows:
> postgres=#  select name,setting,unit from pg_settings where name like
> '%log%';
>              name             |            setting             | unit
> -----------------------------+--------------------------------+------
>   log_autovacuum_min_duration | -1                             | ms
>   log_checkpoints             | off                            |
>   log_connections             | off                            |
>   log_destination             | stderr                         |
>   log_directory               | pg_log                         |
>   log_disconnections          | off                            |
>   log_duration                | off                            |
>   log_error_verbosity         | terse                          |
>   log_executor_stats          | off                            |
>   log_file_mode               | 0600                           |
>   log_filename                | postgresql-%Y-%m-%d_%H%M%S.log |
>   log_hostname                | off                            |
>   log_line_prefix             | %t                             |
>   log_lock_waits              | off                            |
>   log_min_duration_statement  | -1                             | ms
>   log_min_error_statement     | error                          |
>   log_min_messages            | error                          |
>   log_parser_stats            | off                            |
>   log_planner_stats           | off                            |
>   log_rotation_age            | 1440                           | min
>   log_rotation_size           | 10240                          | kB
>   log_statement               | none                           |
>   log_statement_stats         | off                            |
>   log_temp_files              | -1                             | kB
>   log_timezone                | EST5EDT                        |
>   log_truncate_on_rotation    | off                            |
>   logging_collector           | on                             |
>   syslog_facility             | local0                         |
>   syslog_ident                | postgres                       |
>   wal_log_hints               | off                            |
> (30 rows)
>
> postgres=#
>
>
> Thank you.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/27/2015 01:17 PM, anj patnaik wrote:
> Hello,
> Yes, postgres is currently installed on a nfs mounted file system. So
> when graphical installer runs, there is a form which asks location for
> installation. I specified this path /opt/nfsDir/Postgres where nfsDir is
> a nfs mount. So currently this is where PG 9.4 lives.

What is under /opt/nfsDir/Postgres?

>
> My question is when I am given a brand new VM on a different physical
> server, can I mount that same NFS FS and use the Postgres or do I need
> to re-install PG on new VM?

How are you using Postgres now? Please be specific, more detail is
better then less at this point.

>
> I am not sure if PG writes to any other directories besides the one
> where it is installed.


>
> On the issue of logging, I see a lot of log statements because client
> apps do upserts and since I use Tcl I don't have a SQL proc, but rather
> I let it exception and then do an update on the row.

You can Tcl in the database:

http://www.postgresql.org/docs/9.4/interactive/pltcl.html

That will not change things if you let the database throw an exception
there also.

>
> So, you can limit the size of an individual log, but there is no way to
> tell PG to keep the log file short?
>
> if i choose FATAL, I'd lose some log, right?
>
> Thank you!
> ap
>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/27/2015 01:17 PM, anj patnaik wrote:
> Hello,
> Yes, postgres is currently installed on a nfs mounted file system. So
> when graphical installer runs, there is a form which asks location for
> installation. I specified this path /opt/nfsDir/Postgres where nfsDir is
> a nfs mount. So currently this is where PG 9.4 lives.
>
> My question is when I am given a brand new VM on a different physical
> server, can I mount that same NFS FS and use the Postgres or do I need
> to re-install PG on new VM?
>
> I am not sure if PG writes to any other directories besides the one
> where it is installed.
>
> On the issue of logging, I see a lot of log statements because client
> apps do upserts and since I use Tcl I don't have a SQL proc, but rather
> I let it exception and then do an update on the row.
>
> So, you can limit the size of an individual log, but there is no way to
> tell PG to keep the log file short?
>
> if i choose FATAL, I'd lose some log, right?

Hit Enter to quickly. You answered your own question, it is possible to
have Postgres keep the log file short, just do not log anything:) You
have to decide what is more important the size of the file or the
information in it.

>
> Thank you!
> ap
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/30/2015 09:54 AM, anj patnaik wrote:
> 1) directory listing:
>
> /opt/nfsDir/postgres/9.4/
>                                      /bin
>                                      /data
>                                      /etc
>                                     /pgAdmin3
>                                    ....
>
> 2) The way I am using PG now is that I have specified the directory
> above as the location to install it (from graphical installer).
>
> Now, it appears that postgres places files in other directories besides
> the one specified in the installer. For instance, there are scripts in
> /etc/init.d to start the service.

Answers without context are not answers.

Where is /etc/init.d located, the machine that has the NFS mount or
another machine?

>
> So in answering my own question: it appears PG places files in other
> dirs so when I am given a new VM/different physical server with the same
> NFS mount I would either need to copy these files over or better yet
> un-install the current PG and re-install from scratch.

Why not save yourself a lot of trouble, and if it is not the case
already, set up Postgres on a central server and connect to it from
whatever client you wish.

>
> Thanks,
> ap
>
> On Fri, Nov 27, 2015 at 8:30 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 11/27/2015 01:17 PM, anj patnaik wrote:
>
>         Hello,
>         Yes, postgres is currently installed on a nfs mounted file
>         system. So
>         when graphical installer runs, there is a form which asks
>         location for
>         installation. I specified this path /opt/nfsDir/Postgres where
>         nfsDir is
>         a nfs mount. So currently this is where PG 9.4 lives.
>
>
>     What is under /opt/nfsDir/Postgres?
>
>
>         My question is when I am given a brand new VM on a different
>         physical
>         server, can I mount that same NFS FS and use the Postgres or do
>         I need
>         to re-install PG on new VM?
>
>
>     How are you using Postgres now? Please be specific, more detail is
>     better then less at this point.
>
>
>         I am not sure if PG writes to any other directories besides the one
>         where it is installed.
>
>
>
>
>         On the issue of logging, I see a lot of log statements because
>         client
>         apps do upserts and since I use Tcl I don't have a SQL proc, but
>         rather
>         I let it exception and then do an update on the row.
>
>
>     You can Tcl in the database:
>
>     http://www.postgresql.org/docs/9.4/interactive/pltcl.html
>
>     That will not change things if you let the database throw an
>     exception there also.
>
>
>
>         So, you can limit the size of an individual log, but there is no
>         way to
>         tell PG to keep the log file short?
>
>         if i choose FATAL, I'd lose some log, right?
>
>         Thank you!
>         ap
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

1) directory listing:

/opt/nfsDir/postgres/9.4/

                                    /bin

                                    /data

                                    /etc

                                   /pgAdmin3

                                  ....

2) The way I am using PG now is that I have specified the directory above as the location to install it (from graphical installer). 

Now, it appears that postgres places files in other directories besides the one specified in the installer. For instance, there are scripts in /etc/init.d to start the service. 

So in answering my own question: it appears PG places files in other dirs so when I am given a new VM/different physical server with the same NFS mount I would either need to copy these files over or better yet un-install the current PG and re-install from scratch.

Thanks,

ap

On Fri, Nov 27, 2015 at 8:30 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 11/27/2015 01:17 PM, anj patnaik wrote:

Hello,
Yes, postgres is currently installed on a nfs mounted file system. So
when graphical installer runs, there is a form which asks location for
installation. I specified this path /opt/nfsDir/Postgres where nfsDir is
a nfs mount. So currently this is where PG 9.4 lives.

What is under /opt/nfsDir/Postgres?

My question is when I am given a brand new VM on a different physical
server, can I mount that same NFS FS and use the Postgres or do I need
to re-install PG on new VM?

How are you using Postgres now? Please be specific, more detail is better then less at this point.

I am not sure if PG writes to any other directories besides the one
where it is installed.

On the issue of logging, I see a lot of log statements because client
apps do upserts and since I use Tcl I don't have a SQL proc, but rather
I let it exception and then do an update on the row.

You can Tcl in the database:

http://www.postgresql.org/docs/9.4/interactive/pltcl.html

That will not change things if you let the database throw an exception there also.

So, you can limit the size of an individual log, but there is no way to
tell PG to keep the log file short?

if i choose FATAL, I'd lose some log, right?

Thank you!
ap

--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/01/2015 09:16 AM, anj patnaik wrote:
> Thanks for the info Scott.
>
> Can I setup a primary server called A that uses the Postgres
> installation on a nfs mounted filesystem and then A does a daily backup
> of database A and restores to database B on same filesystem.
>
> Then I have server B acting as cold standby and if server A goes down,
> then B would make the backup DB active?
>
> In this case, both server A and B have the same NFS mount, but B is not
> always writing to the data.
>
> The target environment I am moving to is a RAID5 system with mirrored
> discs. I will be getting a VM on a physical server and the mirrored
> disks will store the database. I would like to setup a primary and
> secondary as cold standby.
>
> I am fairly new to PG so asking these questions.
>
> Please advise. Thank you.

Advice, read these pages:

http://www.postgresql.org/docs/9.4/interactive/tutorial-arch.html

http://www.postgresql.org/docs/9.4/interactive/creating-cluster.html

http://www.postgresql.org/docs/9.4/interactive/warm-standby.html

>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/1/15 10:26 AM, Scott Mead wrote:
> The data directory will cause you many problems. You will need one data
> directory that is accessed by one AND ONLY one host for each node
> connected. You can't run an instance on multiple machines pointing to
> the same 'data' directory simultaneously.  Data directories cannot be
> shared by multiple instances simultaneously, that's an active/active
> shared disk cluster and most databases don't support it or require
> massive overhead ( network/licensing I.e. Oracle rac) to do that.

Not only that, but trying to reliably operate a database on an NFS mount
is *extremely* difficult. NFS is notorious for not correctly honoring
things like fsync and atomic rename, which pretty much guarantees you'll
end up with a corrupted (and unusable) database at some point.

VMs can also be dangerous in this regard, though as far as I know all
the major ones should be safe with a default configuration. Be extremely
careful about any "performance tweaks" you decide to do though.

Personally, for small environments, I tend to prefer just running the
database on bare hardware and making use of features like connection
poolers and replication to provide for high availability and operational
flexibility, rather than trying to finagle it through a bunch of
virtualization.

One last think to consider... you didn't mention backups. Understand
that multiple copies of a database kept on the same storage system are
NOT a replacement for backups, no matter how much redundancy that
storage system has. All it takes is one bug in the storage firmware, or
one person hacking into the control interface for the storage system (or
one malicious employee) and ALL of your data goes *poof*. You may well
decide that it's not worth the extra cost/overhead to protect against
those events. Just don't fall into the trap of "we have multiple
snapshots/copies, and those are just as good as backups". :)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com