Re: [GENERAL] Monitoring Replication - Postgres 9.2

On 11/29/2016 6:01 PM, Melvin Davidson wrote: There is no reason you can't execute a cron job on production to a remote db. eg: contents of cron */5 * * * * psql -U postgres -h 123.4.56.789 -d remote_db_name -f /path_to/exec.sql ... The OP wants to run queries on the master and the

Re: [GENERAL] Monitoring Replication - Postgres 9.2

On Tue, Nov 29, 2016 at 8:55 PM, John R Pierce wrote: > On 11/29/2016 5:40 PM, Patrick B wrote: > >> >> >> Can't I do it on the DB size? Using a trigger maybe? instead of using >> Cron? >> > > triggers are only called on database events like insert, update, select. > even

Re: [GENERAL] Monitoring Replication - Postgres 9.2

On 11/29/2016 5:40 PM, Patrick B wrote: Can't I do it on the DB size? Using a trigger maybe? instead of using Cron? triggers are only called on database events like insert, update, select. even something like the pgagent scheduler thats frequently bundled with pgadmin uses cron to run

Re: [GENERAL] Monitoring Replication - Postgres 9.2

2016-11-30 14:21 GMT+13:00 John R Pierce : > On 11/29/2016 5:10 PM, Patrick B wrote: > > > Yep.. once a minute or so. And yes, I need to store a history with > timestamp. > > Any idea? :) > > > so create a table with a timestamptz, plus all the fields you want, have a >

Re: [GENERAL] Monitoring Replication - Postgres 9.2

On 11/29/2016 5:10 PM, Patrick B wrote: Yep.. once a minute or so. And yes, I need to store a history with timestamp. Any idea? :) so create a table with a timestamptz, plus all the fields you want, have a script (perl? python? whatever your favorite poison is with database access)

Re: [GENERAL] Monitoring Replication - Postgres 9.2

2016-11-30 14:02 GMT+13:00 John R Pierce : > On 11/29/2016 3:31 PM, Patrick B wrote: > > I use these queries to monitor the streaming replication: > > *on master:* > select client_addr, state, sent_location, write_location, flush_location, > replay_location, sync_priority

Re: [GENERAL] Monitoring Replication - Postgres 9.2

On 11/29/2016 3:31 PM, Patrick B wrote: I use these queries to monitor the streaming replication: *on master:* select client_addr, state, sent_location, write_location, flush_location, replay_location, sync_priority from pg_stat_replication; *On slave:* select now() -

Re: [GENERAL] "Fuzzy" Matches on Nicknames

Hello Michael, On Tue, 2016-11-29 at 19:10 -0500, Michael Sheaver wrote: > Greetings, > > I have two tables that are populated using large datasets from > disparate external systems, and I am trying to match records by > customer name between these two tables. I do not have any > authoritative

[GENERAL] "Fuzzy" Matches on Nicknames

Greetings, I have two tables that are populated using large datasets from disparate external systems, and I am trying to match records by customer name between these two tables. I do not have any authoritative key, such as customerID or nationalID, by which I can match them up, and I have

Re: [GENERAL] Index size

On 11/29/2016 03:30 PM, Samuel Williams wrote: I'd like to understand a bit more about indexes in PG. https://www.postgresql.org/docs/9.5/static/indexam.html "An index is effectively a mapping from some data key values to tuple identifiers, or TIDs, of row versions (tuples) in the index's

[GENERAL] Monitoring Replication - Postgres 9.2

Hi guys, I use these queries to monitor the streaming replication: *on master:* select client_addr, state, sent_location, write_location, flush_location, replay_location, sync_priority from pg_stat_replication; *On slave:* select now() - pg_last_xact_replay_timestamp() AS replication_delay;

[GENERAL] Index size

I'd like to understand a bit more about indexes in PG. When I have a row in a table, and an index, say, for a single column, does that duplicate the entire row on disk? Or is there some kind of id lookup involved? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] How to migrate from PGSQL 9.3 to 9.6

On 11/29/2016 2:20 PM, Sinclair, Ian D (Ian) wrote: I’ve found the RPMS I need, but I’ve come across a setup file that I think I need to get a correct version of. I found references to postgresql-setup, but I have a feeling that I may need a version specific copy of that script. If I get the

[GENERAL] How to migrate from PGSQL 9.3 to 9.6

I've never worked with Postgres, but I've been tasked with upgrading our product from using 9.3 to 9.6. Please feel free to direct me to appropriate newbie docs. I've found the RPMS I need, but I've come across a setup file that I think I need to get a correct version of. I found references to

Re: [GENERAL] Backup "Best Practices"

On Mon, Nov 28, 2016 at 11:20 PM, Thomas Kellerer wrote: > Israel Brewster schrieb am 28.11.2016 um 23:50: > >> >>> pg_archivecleanup -n /mnt/server/archiverdir >>> 00010010.0020.backup >>> >> >> Ok, but where does that

Re: [GENERAL] Backup "Best Practices"

On Nov 29, 2016, at 8:12 AM, Israel Brewster wrote: > > On Nov 28, 2016, at 10:04 PM, Jeff Janes > wrote: >> >> On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster >

Re: [GENERAL] Backup "Best Practices"

On Nov 28, 2016, at 10:20 PM, Thomas Kellerer wrote: > > Israel Brewster schrieb am 28.11.2016 um 23:50: >>> >>> pg_archivecleanup -n /mnt/server/archiverdir >>> 00010010.0020.backup >> >> Ok, but where does that

Re: [GENERAL] Backup "Best Practices"

On Nov 28, 2016, at 10:04 PM, Jeff Janes wrote: > > On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster > wrote: > >> - What is the "best" (or just a good) method of keeping the WAL archives >> under control? Obviously

Re: [GENERAL] Invoice Table Design

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

Re: [GENERAL] Invoice Table Design

- Original Message - > From: "rob" > To: pgsql-general@postgresql.org > Sent: Tuesday, November 29, 2016 3:45:21 AM > Subject: Re: [GENERAL] Invoice Table Design > > Hi Rich, > > thanks for the response -- going from Mongo to Postgres does require the > kind of

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

On 11/29/2016 01:15 AM, Thomas Güttler wrote: Am 28.11.2016 um 16:01 schrieb Adrian Klaver: On 11/28/2016 06:28 AM, Thomas Güttler wrote: Hi, PostgreSQL is rock solid and one of the most reliable parts of our toolchain. Thank you Up to now, we don't store files in PostgreSQL. I was

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

On 11/29/2016 01:50 AM, Thomas Güttler wrote: Am 29.11.2016 um 01:52 schrieb Mike Sofen: From: Thomas Güttler Sent: Monday, November 28, 2016 6:28 AM ...I have 2.3TBytes of files. File count is 17M Since we already store our structured data in postgres, I think about storing the files in

Re: [GENERAL] Rounding Problems?

elbriga wrote: > Thanks for the detailed answer! > > Changing the function sinature seams to have solved the problem: > CREATE OR REPLACE FUNCTION ceilDecimal(num numeric) RETURNS float AS > $BODY$ > BEGIN > RETURN CEIL(num * 100) / 100; > END > $BODY$ > LANGUAGE 'plpgsql'; > > > SELECT

Re: [GENERAL] Rounding Problems?

Thanks for the detailed answer! Changing the function sinature seams to have solved the problem: CREATE OR REPLACE FUNCTION ceilDecimal(num numeric) RETURNS float AS $BODY$ BEGIN RETURN CEIL(num * 100) / 100; END $BODY$ LANGUAGE 'plpgsql'; SELECT ceilDecimal(0.07); ceildecimal

Re: [GENERAL] Rounding Problems?

elbriga wrote: > Hi, > I have this pl function: > CREATE OR REPLACE FUNCTION ceilDecimal(num float) RETURNS float AS > $BODY$ > BEGIN > RETURN CEIL(num * 100) / 100; > END > $BODY$ > LANGUAGE 'plpgsql'; > > It is supposed to do a "decimail ceil" for 2 decimal places. > But when I do

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

Thomas Güttler writes: > I have 2.3TBytes of files. File count is 17M > > Up to now we use rsync (via rsnapshot) to backup our data. Isn't putting those files into your database going to make any sort of maintanance on your database cumbersome? How big is your

[GENERAL] Rounding Problems?

Hi, I have this pl function: CREATE OR REPLACE FUNCTION ceilDecimal(num float) RETURNS float AS $BODY$ BEGIN RETURN CEIL(num * 100) / 100; END $BODY$ LANGUAGE 'plpgsql'; It is supposed to do a "decimail ceil" for 2 decimal places. But when I do "SELECT ceilDecimal(0.07)" It will

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

On 29 November 2016 at 16:50, Thomas Güttler wrote: > > > Am 29.11.2016 um 01:52 schrieb Mike Sofen: > >> From: Thomas Güttler Sent: Monday, November 28, 2016 6:28 AM >> >> ...I have 2.3TBytes of files. File count is 17M >> >> Since we already store our structured

Re: [GENERAL] pg_rewind rewinded too much...

On Tue, Nov 29, 2016 at 09:30:26AM +0100, ma...@kset.org wrote: > I am doing some testing with pg_rewind and I encountered a very strange > situation. So, let me describe the scenario: > > 1. Create a master (server1) node (max_wal_size = 2GB, checkpoint_timeout = > 10min) > 2. Create a test

Re: [GENERAL] Invoice Table Design

rob wrote: Hi Rich, thanks for the response -- going from Mongo to Postgres does require the kind of approach you suggest. I suppose my question was a little bit more along the lines if anyone has experience with designing payment / invoicing systems and any caveats they may have encountered

Re: [GENERAL] Wal files - Question | Postgres 9.2

2016-11-29 16:36 GMT+13:00 David G. Johnston : > On Mon, Nov 28, 2016 at 8:22 PM, Patrick B > wrote: > >> >> Ho >> ​[w] >> is that even possible?? I don't understand! >> >> > ​https://www.postgresql.org/docs/9.2/static/warm-standby.html >

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

2 other options that you may want to look at : - cephfs This has nothing to do with postgres but is a distributed filesystem handling very large amount of files (thinks next generation NFS) I haven't tried it myself yet but they reached a "stable" milestone regarding the distributed fs. cf

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

Am 29.11.2016 um 01:52 schrieb Mike Sofen: From: Thomas Güttler Sent: Monday, November 28, 2016 6:28 AM ...I have 2.3TBytes of files. File count is 17M Since we already store our structured data in postgres, I think about storing the files in PostgreSQL, too. Is it feasible to store file

We reached the limit of inotify. Was: [GENERAL] Storing files: 2.3TBytes, 17M file count

Am 28.11.2016 um 17:43 schrieb Daniel Verite: Thomas Güttler wrote: Up to now we use rsync (via rsnapshot) to backup our data. But it takes longer and longer for rsync to detect the changes. Rsync checks many files. But daily only very few files really change. More than 99.9% don't.

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

Am 28.11.2016 um 16:01 schrieb Adrian Klaver: On 11/28/2016 06:28 AM, Thomas Güttler wrote: Hi, PostgreSQL is rock solid and one of the most reliable parts of our toolchain. Thank you Up to now, we don't store files in PostgreSQL. I was told, that you must not do this But this was

Re: [GENERAL] Invoice Table Design

Hi Rich, thanks for the response -- going from Mongo to Postgres does require the kind of approach you suggest. I suppose my question was a little bit more along the lines if anyone has experience with designing payment / invoicing systems and any caveats they may have encountered along the way.

[GENERAL] pg_rewind rewinded too much...

Hi, I am doing some testing with pg_rewind and I encountered a very strange situation. So, let me describe the scenario: 1. Create a master (server1) node (max_wal_size = 2GB, checkpoint_timeout = 10min) 2. Create a test database on it (server1) 3. Create a test table (server1) 4. Insert a