Re: [GENERAL] delete a file everytime pg server starts/crashes

[Ray Cote]

>
> On Thu, Oct 5, 2017 at 10:04 AM, athinivas  wrote:
>
>> Hi,
>>
>> I'm having a requirement to delete a file in system whenever pg server is
>> started/crashed. Any idea?
>>
>> Thanks,
>> Athi
>>
>>
If you’re running on Linux you can modify the init.d (or service) file and
add a line to delete the file.
To remove on crash is likely to require some sort of ongoing monitoring
service to see that the process is no longer running.
If you’re already using a service (such as monit) which brings an
application back up after crash then adding a line to the init.d file may
be sufficient.
—Ray

--
Raymond Cote, President
voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype:
ray.cote
Schedule a meeting: https://calendly.com/ray_cote/60min/

[GENERAL] Prudent practices for PostgreSQL disk configuration?

[Ray Cote]

Hello:

I’m about to upgrade my disk configuration and looking for prudent disk
configuration practices for running PostgreSQL 9.3 on RHEL6 in a VMWare
environment in a shared hosting facility — physical disk not SSD. This is a
write-heavy transactional application. 8 Cpu cores (if it matters)

Things I (think I) know to do:
- Separate database storage, WAL, and standard logs into different
partitions.
- 8K disk blocks.
- Use ext4 vs. XFS(?)
- data=writeback,noatime,nodiratime

Any other disk-specific settings you would recommend as a starting point
for initial testing?
Appreciate any thoughts.

—Ray

Re: [GENERAL] PostgreSQL Developer Best Practices

[Ray Cote]

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert karsten.hilb...@gmx.net
wrote:

  1. Prefix ALL literals with an Escape
 EG:  SELECT E'This is a \'quoted literal \'';
  SELECT E'This is an unquoted literal';
 
 Doing so will prevent the annoying WARNING:  nonstandard use of
 escape in a string literal


I'd be concerned that what is missing here is the bigger issue of  Best
Practice #0: Use Bound Variables.
The only way I've seen invalid literals show up in SQL queries is through
the dynamic generation of SQL Statements vs. using bound variables.
Not using bound variables is your doorway to SQL injection exploits.


9. Do NOT arbitrarily assign an id column to a table as a primary key
 when other columns
 are perfectly suited as a unique primary key.

...

Good example:
 CREATE TABLE accounts
 ( accout_id bigint NOT NULL ,


I would not consider the general use of natural primary keys to be best
practice.
Let's assume your account_id field is used as a foreign key in a dozen
other tables.
1) What happens if someone mis-types the account-id?
 To correct that, you also need to correct the FK field in the other
dozen tables.
2) What happens when your company starts a new project (or buys a
competitor) and all the new account numbers are alpha-numeric?
3) Your example shows the id as a bigint, but your rule is not limited to
integers.
What if your table is country populations and the primary key is country
name?
Now, you have quite large foreign keys (and a country changing its name is
not unheard of).
(and let's not even get started on case-sensitivity or character encodings).

-- 
Raymond Cote, President
voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype:
ray.cote

Re: [GENERAL] On using doubles as primary keys

[Ray Cote]

On Fri, Apr 17, 2015 at 11:56 AM, David G. Johnston 
david.g.johns...@gmail.com wrote:

 ​
 MD
 ​
 I'm not sure what you mean by doubles. Do you mean bigint data type, or do
 you mean use two columns for a primary key? Either way it's pretty simple.
 ​
 MD
 ​
 If you mean a bigint, then probably best to use serial data type, which
 will default to the next value.
 ​
 MD
 ​
 If you mean use two columns for a Primary Key, the you just specify the
 columns.
 ​
 MD
 ​
 EG: CONSTRAINT PRIMARY KEY table_name_pk PRIMARY KEY (col1, col2)

 ​I take it the OP means double precision ​

 http://www.postgresql.org/docs/9.0/static/datatype-numeric.html

 I'd be suspect of choosing a PK whose type definition includes the word
 inexact.

 You also say you want to store the data as a double precision but you
 never actually explain what the natural key of the data is.

 If you are strictly storing serial (big) integers but doing so within a
 double precision typed column you may be OK - but I would advise taking
 input from someone with more expertise on the properties of IEEE floating
 point numbers.

 David J.



(Not an IEEE floating point expert, but...) I've learned the hard way to
never rely on comparing two floating point numbers for equality -- and
that's what you are doing if you join on them as primary keys. If you must
use the underlying numeric data for joining, I'd recommend you do something
like:
  * output the value to a string (rounded to a specific number of digits)
  * use the textual representation of the number as your primary key.

Alternatively, if the numbers are very large or very small, you may want to
try converting the binary data into a BINHEX value and use that textual
representation as the primary key.

Either way, I think you'd be saving yourself a lot of headaches using a
representation that is not 'inexact.'
--Ray

-- 
Raymond Cote, President
voice: +1.603.924.6079 email: rgac...@appropriatesolutions.com skype:
ray.cote

[GENERAL] Database performs massive reads when I'm doing writes.

[Ray Cote]

Hello:

I have a PostgreSQL 9.0.3 database that has suddenly started exhibiting odd 
read behavior.

The version number is:
PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 
20080704 (Red Hat 4.1.2-50), 64-bit
This was compiled from source about a year ago and has been running just fine. 
The database feeds a very low-volume web site (a few hits per minute).

Starting a few days ago we started to see a strange behavior where writing to 
the database causes massive read operations. 
For example, I have a table that needs to be updated every night (about 20,000 
rows). 
Using Django ORM, we update them one item at a time. 
Usually the overall process takes a few minutes; it is now taking hours (like 
over 15 hours).

Running atop, we're seeing Read Disk values in the range of 147.2M/10s and 
Write Disk values in the range of 16K/10s.
Together, the Disk throughput is in the high 90% and frequently hits 100%.

When I'm not writing to the database, it becomes quiet without any disk 
activity. 

This is a 256 MByte system and atop shows we're not swapping. 

There's nothing unusual in the PostgreSQL logs. 

Appreciate any pointers as to how I go about identifying the root cause of this 
problem. 

Thanks.

--Ray


-- 
Ray Cote, President Appropriate Solutions, Inc. 
We Build Software 
www.AppropriateSolutions.com 603.924.6079 


-- 
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] Database performs massive reads when I'm doing writes.

[Ray Cote]

- Original Message -

 From: bricklen brick...@gmail.com
 To: Ray Cote rgac...@appropriatesolutions.com
 Cc: pgsql-general@postgresql.org
 Sent: Thursday, June 6, 2013 3:44:04 PM
 Subject: Re: [GENERAL] Database performs massive reads when I'm doing
 writes.

 On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote 
 rgac...@appropriatesolutions.com  wrote:

  Starting a few days ago we started to see a strange behavior where
  writing to the database causes massive read operations.
 
  For example, I have a table that needs to be updated every night
  (about 20,000 rows).
 
  Using Django ORM, we update them one item at a time.
 
  Usually the overall process takes a few minutes; it is now taking
  hours (like over 15 hours).
 

  Running atop, we're seeing Read Disk values in the range of
  147.2M/10s and Write Disk values in the range of 16K/10s.
 
  Together, the Disk throughput is in the high 90% and frequently
  hits
  100%.
 

  When I'm not writing to the database, it becomes quiet without any
  disk activity.
 

  This is a 256 MByte system and atop shows we're not swapping
 
 Is autovacuum set to 'on'?

 Possibly there is bloat in your table and indexes; what happens to
 the system after you issue a manual VACUUM ANALYZE VERBOSE? If
 that helps, you might need to make your autovacuum settings more
 aggressive.

Yes Autovacuum is set to 'on'. 
I'll run a Vacuum Analyze Verbose after the data load finally completes (which 
could easily be another two hours). 
Don't want to touch the system until this data finally gets loaded. 

Thanks for the recommendation. 
--Ray 

-- 

Ray Cote, President Appropriate Solutions, Inc. 
We Build Software 
www.AppropriateSolutions.com 603.924.6079 

Re: [GENERAL] Database performs massive reads when I'm doing writes.

[Ray Cote]

It is Postmaster itself: 
11068 - 315.9M 136K 0K 85% postmaster 
11000 - 56808K 8K 0K 15% postmaster 
11003 - 0K 80K 0K 0% postmaster 
11004 - 0K 24K 0K 0% postmaster 
11067 - 0K 0K 0K 0% load_rets.py 

The above are the atop lines for just postmaster. This is a 10s snapshot so you 
can see lots of read activity. 
The load_rets.py task is the Python script loading the database. 

--Ray 

- Original Message -

 From: Jeff Janes jeff.ja...@gmail.com
 To: Ray Cote rgac...@appropriatesolutions.com
 Cc: pgsql-general@postgresql.org
 Sent: Thursday, June 6, 2013 4:05:28 PM
 Subject: Re: [GENERAL] Database performs massive reads when I'm doing
 writes.

 On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote 
 rgac...@appropriatesolutions.com  wrote:

  Hello:
 

  I have a PostgreSQL 9.0.3 database that has suddenly started
  exhibiting odd read behavior.
 

  The version number is:
 
  PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
  (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit
 
  This was compiled from source about a year ago and has been running
  just fine.
 
  The database feeds a very low-volume web site (a few hits per
  minute).
 

  Starting a few days ago we started to see a strange behavior where
  writing to the database causes massive read operations.
 
  For example, I have a table that needs to be updated every night
  (about 20,000 rows).
 
  Using Django ORM, we update them one item at a time.
 
  Usually the overall process takes a few minutes; it is now taking
  hours (like over 15 hours).
 

  Running atop, we're seeing Read Disk values in the range of
  147.2M/10s and Write Disk values in the range of 16K/10s.
 
  Together, the Disk throughput is in the high 90% and frequently
  hits
  100%.
 

 Which specific processes are using the disk? (on my atop, if I hit
 'c' it will change mode to show me the entire so-called command
 line, which will let you know if the culprit is a vacuum worker,
 the checkpointer, or someone else).

 Cheers,

 Jeff
-- 

Ray Cote, President Appropriate Solutions, Inc. 
We Build Software 
www.AppropriateSolutions.com 603.924.6079 

Re: [GENERAL] Database performs massive reads when I'm doing writes.

[Ray Cote]

And... it was a rookie mistake. 
In answering the last question I once again looked at the database and realized 
there was an index missing! 
Swear it has been there before (and I should have double-checked sooner). 
Added the index and now we're moving right along. 

Thanks for all leads everyone sent me. 
Learned a lot about Postgresql today. 
--Ray 

- Original Message -

 From: Ray Cote rgac...@appropriatesolutions.com
 To: Jeff Janes jeff.ja...@gmail.com
 Cc: pgsql-general@postgresql.org
 Sent: Thursday, June 6, 2013 5:12:51 PM
 Subject: Re: [GENERAL] Database performs massive reads when I'm doing
 writes.

 It is Postmaster itself:
 11068 - 315.9M 136K 0K 85% postmaster
 11000 - 56808K 8K 0K 15% postmaster
 11003 - 0K 80K 0K 0% postmaster
 11004 - 0K 24K 0K 0% postmaster
 11067 - 0K 0K 0K 0% load_rets.py

 The above are the atop lines for just postmaster. This is a 10s
 snapshot so you can see lots of read activity.
 The load_rets.py task is the Python script loading the database.

 --Ray

 - Original Message -

  From: Jeff Janes jeff.ja...@gmail.com
 
  To: Ray Cote rgac...@appropriatesolutions.com
 
  Cc: pgsql-general@postgresql.org
 
  Sent: Thursday, June 6, 2013 4:05:28 PM
 
  Subject: Re: [GENERAL] Database performs massive reads when I'm
  doing
  writes.
 

  On Thu, Jun 6, 2013 at 9:53 AM, Ray Cote 
  rgac...@appropriatesolutions.com  wrote:
 

   Hello:
  
 

   I have a PostgreSQL 9.0.3 database that has suddenly started
   exhibiting odd read behavior.
  
 

   The version number is:
  
 
   PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC
   gcc
   (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit
  
 
   This was compiled from source about a year ago and has been
   running
   just fine.
  
 
   The database feeds a very low-volume web site (a few hits per
   minute).
  
 

   Starting a few days ago we started to see a strange behavior
   where
   writing to the database causes massive read operations.
  
 
   For example, I have a table that needs to be updated every night
   (about 20,000 rows).
  
 
   Using Django ORM, we update them one item at a time.
  
 
   Usually the overall process takes a few minutes; it is now taking
   hours (like over 15 hours).
  
 

   Running atop, we're seeing Read Disk values in the range of
   147.2M/10s and Write Disk values in the range of 16K/10s.
  
 
   Together, the Disk throughput is in the high 90% and frequently
   hits
   100%.
  
 

  Which specific processes are using the disk? (on my atop, if I hit
  'c' it will change mode to show me the entire so-called command
  line, which will let you know if the culprit is a vacuum worker,
  the checkpointer, or someone else).
 

  Cheers,
 

  Jeff
 
 --

 Ray Cote, President Appropriate Solutions, Inc.
 We Build Software
 www.AppropriateSolutions.com 603.924.6079

-- 

Ray Cote, President Appropriate Solutions, Inc. 
We Build Software 
www.AppropriateSolutions.com 603.924.6079 

Re: [GENERAL] Database performs massive reads when I'm doing writes.

[Ray Cote]

- Original Message -

 From: Jeff Janes jeff.ja...@gmail.com
 To: Ray Cote rgac...@appropriatesolutions.com
 Cc: pgsql-general@postgresql.org
 Sent: Thursday, June 6, 2013 5:41:00 PM
 Subject: Re: [GENERAL] Database performs massive reads when I'm doing
 writes.

 On Thu, Jun 6, 2013 at 2:12 PM, Ray Cote 
 rgac...@appropriatesolutions.com  wrote:

  It is Postmaster itself:
 
  11068 - 315.9M 136K 0K 85% postmaster
 
  11000 - 56808K 8K 0K 15% postmaster
 
  11003 - 0K 80K 0K 0% postmaster
 
  11004 - 0K 24K 0K 0% postmaster
 
  11067 - 0K 0K 0K 0% load_rets.py
 

  The above are the atop lines for just postmaster. This is a 10s
  snapshot so you can see lots of read activity.
 
  The load_rets.py task is the Python script loading the database.
 

 The postmaster has many children, each of which is responsible for
 something different. If you display the full command line rather
 than just the abbreviated one, it will give you more info on exactly
 which child is using the disk, for example:

 PID DSK COMMAND-LINE

 3950 16% postgres: jjanes jjanes [local] UPDATE
 2978 0% postgres: checkpointer process

 2982 0% postgres: stats collector process

 Cheers,

 Jeff
Hi Jeff: 

I can see that now after pressing c in disk display mode: 
19007 - D 86% postgres: postgres petersons 127.0.0.1(59702) UPDATE 
11003 - S 12% postgres: writer process 
11004 - S 2% postgres: wal writer process 

This is how it looks when running properly (doing a big update at the moment). 
Thanks for the lead. Learning much about atop today. 
--Ray 

-- 

Ray Cote, President Appropriate Solutions, Inc. 
We Build Software 
www.AppropriateSolutions.com 603.924.6079