Re: [GENERAL] Stored procedure version control
Neil Anderson wrote: On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? I can't comment from the POV of those who represent Postgres, but I used to work for a company who specialised in change management for database products, SQL Server and Oracle in particular. There are at least two approaches. The migrations approach and the state based approach. [etc.] Thanks everybody, summary passed on. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] How safe is pg_basebackup + continuous archiving?
On 29.06.2016 17:51, Kaixi Luo wrote: Hello, We use PostgreSQL at work and we do daily backups with pg_dump. After that we pg_restore the dump and check the database that there isn't any data corruption. As the database grows, the whole pg_dump / pg_restore cycle time is quickly approaching 24h, so we need to change strategies. We've thought about using pg_basebackup + continuous archiving as an alternative backup process, but I have doubts regarding the safety of such procedure. As far as I know, pg_basebackup is done via rsync (and we also archive wals using rsync), so if by any chance disk corruption occurs on the master server, the corruption would be carried over to our backup server. How can we check for backup corruption in this case? Thanks you very much. Kaixi Luo Hello! Only pg_dump+ data checksums turned on can make you "sure" in absence corruption in backup. But! If at any way you've lost some data file from relation or it zeroed by say powerloss(there were some issue with xfs in the past) even with pg_dump you will never know it. But there is no any other method in PG to check database for corruption. Also PG have no checksums on clog's file. So if any corruption is happend in this file you also will never know it. So at now pg_basebackup+ wal archiving is like walking on the minefield with tightly closed eyes . You never know when it will make explode! Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Log archiving failing. Seems to be wrong timeline
Hello, We have 2 postgresql servers (v 9.4.2) master and slave in streaming replication. The overall cluster is controlled using pacemaker & corosync and the pgsql cluster agent which handles failover to, and promotion of, the slave. Recently a failover occured and I noticed that log archiving was failing on the master: cp: cannot stat 'pg_xlog/00020002': No such file or directory 2016-06-30 11:49:48 BST [13816]: [1235-1] db=,user=,client= LOG: archive command failed with exit code 1 2016-06-30 11:49:48 BST [13816]: [1236-1] db=,user=,client= DETAIL: The failed archive command was: cp pg_xlog/00020002 /mnt/pgsql/data/pg_archive/00020002 cp: cannot stat 'pg_xlog/00020002': No such file or directory 2016-06-30 11:49:49 BST [13816]: [1237-1] db=,user=,client= LOG: archive command failed with exit code 1 2016-06-30 11:49:49 BST [13816]: [1238-1] db=,user=,client= DETAIL: The failed archive command was: cp pg_xlog/00020002 /mnt/pgsql/data/pg_archive/00020002 2016-06-30 11:49:49 BST [13816]: [1239-1] db=,user=,client= WARNING: archiving transaction log file "00020002" failed too many times, will try again later But the timeline we're on is different: # /usr/lib/postgresql/9.4/bin/pg_controldata /mnt/pgsql/data pg_control version number:942 Catalog version number: 201409291 Database system identifier: 6198394727571912088 Database cluster state: in production pg_control last modified: Thu 30 Jun 2016 11:42:42 BST Latest checkpoint location: 2/EEE842E8 Prior checkpoint location:2/EED64F68 Latest checkpoint's REDO location:2/EEE4B610 Latest checkpoint's REDO WAL file:002C000200EE Latest checkpoint's TimeLineID: 44 Latest checkpoint's PrevTimeLineID: 44 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/2947680 Latest checkpoint's NextOID: 74375 Latest checkpoint's NextMultiXactId: 464 Latest checkpoint's NextMultiOffset: 929 Latest checkpoint's oldestXID:677 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 2947680 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Time of latest checkpoint:Thu 30 Jun 2016 11:42:27 BST Fake LSN counter for unlogged rels: 0/1 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location:0/0 Backup end location: 0/0 End-of-backup record required:no Current wal_level setting:hot_standby Current wal_log_hints setting:off Current max_connections setting: 250 Current max_worker_processes setting: 8 Current max_prepared_xacts setting: 10 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Why are we trying to archive logs which belong to an old timeline? Any thoughts much appreciated. Regards Chris -- 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] How safe is pg_basebackup + continuous archiving?
> > Before replaying a backup on a production system, you would need a > pre-production setup where the backup is replayed and checked. > Honestly, you can only be sure that a backup is working correctly > after reusing it. You could always do some validation of the raw > backup contents, but you need at the end the WAL applied on top of it > to be able to check the status of a server that has reached a > consistent point. > Could you elaborate a bit more on this last part? If a PostgreSQL recovery from a pg_basebackup reaches a consistent point and is able to start up fully, that is not a guarantee that there hasn't been any underlying corruption, as far as I know. Am I correct on this? Thanks once again. Kaixi
Re: [GENERAL] Stored procedure version control
-Original Message-
>From: Mark Morgan Lloyd Sent: Thursday, June 30, 2016 2:41 AM
>Neil Anderson wrote:
>> On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:
>>> Elsewhere, somebody was asking how people implemented version control
>>> for stored procedures on (MS) SQL Server.
>>>
>>> The consensus was that this is probably best managed by using scripts
>>> or command files to generate stored procedures etc., but does anybody
>>> have any comment on that from the POV of PostgreSQL?
>>>
> [etc.] Thanks everybody, summary passed on.
>Mark Morgan Lloyd
>markMLl .AT. telemetry.co .DOT. uk
A bit late to the thread, but here's some specific details on how I've
implemented version control in PG 9.5, in a small team environment deploying
to single database servers in each tier (dev, qa, stage, prod). It's
working well so far, and allows my stored proc versions to be aligned with
the middle and upper tier code releases. I'm the lead database
architect-engineer for a brand new genomics application (lots of data).
Details:
- we're using git for version control, with a base name for each repo that
holds a single micro-service (like "JobManager") and a suffix for the data
tier code ("JobManagerDBMS") making it simple for devops to find the related
code for a micro-service deployment by repo.
- within a DBMS repo, I've got subfolders like "scripts", "sprocs",
"documentation", where scripts holds ad hoc scripts that need to be run
during a deployment (might be adjusting DDL or seeding or cleaning up data),
sprocs for stored function files that must be compiled into PG, and
documentation holds notes, data models, etc. We have a simple python script
that compiles/recompiles all stored proc files within a named folder -
deployment done with one call.
- I only code using source code files, by cloning an existing suitable base
stored proc (akin to a template) to a new file name (like a "get" or "set"
stored proc) and then revising to match the requirement. In a detailed
comment block within each stored proc, I list a version number (just for
reference, not used programmatically at this point), change history, author,
comments, and one or more sample calls that form the basis of my unit tests.
- after I've finished the requested work and the stored procs are working
as expected, I update a Version file in the folder, push it into git and
merge it into the development branch.
- let's say a new enhancement request comes in. I create a new branch
(like "RequestForNewThingy"), version that, do the work and merge it back in
just like the above. So we've got isolation and persistence of changes.
- I happen to be using the new DataGrip code editor, which supports this
beautifully, since my git tree appears on the right side of editor window,
allowing me directly edit/clone without leaving the editor.
My coding efficiency using this model is quite high...the overhead of using
git is trivial.
For rollbacks, we can simply point to the prior stored proc version and
recompile those. For DDL rollbacks, I have to code those scripts and supply
them...this is the one place I have to spend a bit more time creating a more
automated solution. I'd love to hear how other folks have solved
programmatic rollbacks.
Mike Sofen (San Diego, CA USA)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replication with non-read-only standby.
Setup: 2 PostgreSQL servers are geographically spread. The first one is used for an application that gathers data. It is connected to the second database that is used to process the said data. Connection is not very stable nor is it fast, so using Bidirectional replication is not an option. It is OK if data is shipped in batches rather than streamed. Question: Is there a way to make the standby server non-read-only, so that it can keep getting updates (mostly inserts) from the 'master', but users are able to edit the data stored on 'slave'? Is there some alternative solution to this? Regards, Nick. -- 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] How safe is pg_basebackup + continuous archiving?
On Thu, Jun 30, 2016 at 9:00 PM, Kaixi Luo wrote: >> Before replaying a backup on a production system, you would need a >> pre-production setup where the backup is replayed and checked. >> Honestly, you can only be sure that a backup is working correctly >> after reusing it. You could always do some validation of the raw >> backup contents, but you need at the end the WAL applied on top of it >> to be able to check the status of a server that has reached a >> consistent point. > > > Could you elaborate a bit more on this last part? If a PostgreSQL recovery > from a pg_basebackup reaches a consistent point and is able to start up > fully, that is not a guarantee that there hasn't been any underlying > corruption, as far as I know. Am I correct on this? You are correct, that's why you need a copycat of the production system that is a pre-stage of the production stage, where the backups are replayed and checked with an application that replays the patterns of the production application. Applying extra checks on top of that is good as well: pg_dump, data checksums, index consistency checks (this makes regret that we don't have pg_amcheck in core yet actually), etc. -- Michael -- 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] How safe is pg_basebackup + continuous archiving?
Greetings, * Kaixi Luo (kaixi...@gmail.com) wrote: > We use PostgreSQL at work and we do daily backups with pg_dump. After that > we pg_restore the dump and check the database that there isn't any data > corruption. As the database grows, the whole pg_dump / pg_restore cycle > time is quickly approaching 24h, so we need to change strategies. I've found this to be a good strategy also, but it's far from perfect. Corruption can still occur, for example, in indexes on the primary system. Generally speaking, pg_dump doesn't exercise indexes and therefore you won't notice if an index is corrupt. > We've thought about using pg_basebackup + continuous archiving as an > alternative backup process, but I have doubts regarding the safety of such > procedure. As far as I know, pg_basebackup is done via rsync (and we also > archive wals using rsync), so if by any chance disk corruption occurs on > the master server, the corruption would be carried over to our backup > server. This is correct, but checksums are now available in modern versions of PG, which will detect disk corruption. Those checksums would be carried over to the backup server and could be verified there by using pg_dump (note that this still wouldn't help with indexes, but you don't have coverage there today anyway). > How can we check for backup corruption in this case? Thanks you very much. There has been some discussion about a specific tool for checking the checksums throughout the entire system. I don't know of anyone activly working on that, unfortunately. There are a number of tools available to help with online backups and continuous archiving beyond pgbasebackup and having to hand-roll scripts. I'm personally biased towards and prefer pgBackRest, as I helped start that project, but there are other tools, such as barman and WAL-E, which would still be better than trying to implement everything correctly on your own. Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] Stored procedure version control
Mike Sofen wrote: -Original Message- From: Mark Morgan Lloyd Sent: Thursday, June 30, 2016 2:41 AM Neil Anderson wrote: On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? [etc.] Thanks everybody, summary passed on. Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk A bit late to the thread, but here's some specific details on how I've implemented version control in PG 9.5, in a small team environment deploying to single database servers in each tier (dev, qa, stage, prod). It's working well so far, and allows my stored proc versions to be aligned with the middle and upper tier code releases. I'm the lead database architect-engineer for a brand new genomics application (lots of data). Thanks Mike, I'll pass that on if the thread on CIX still looks live. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Replication with non-read-only standby.
On Thu, Jun 30, 2016 at 7:15 AM, Nick Babadzhanian wrote: > Setup: > 2 PostgreSQL servers are geographically spread. The first one is used for an > application that gathers data. It is connected to the second database that is > used to process the said data. Connection is not very stable nor is it fast, > so using Bidirectional replication is not an option. It is OK if data is > shipped in batches rather than streamed. > > Question: > Is there a way to make the standby server non-read-only, so that it can keep > getting updates (mostly inserts) from the 'master', but users are able to > edit the data stored on 'slave'? Is there some alternative solution to this? I'd probably solve this with slony. -- 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] Stored procedure version control
On Wed, Jun 29, 2016 at 1:46 PM, Neil Anderson wrote: > On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: >> >> Elsewhere, somebody was asking how people implemented version control >> for stored procedures on (MS) SQL Server. >> >> The consensus was that this is probably best managed by using scripts or >> command files to generate stored procedures etc., but does anybody have >> any comment on that from the POV of PostgreSQL? >> > > I can't comment from the POV of those who represent Postgres, but I used to > work for a company who specialised in change management for database > products, SQL Server and Oracle in particular. There are at least two > approaches. The migrations approach and the state based approach. > > For migrations you create up and down scripts/code fragments to move the > database through versions over time, committing them to a source control > system as you go. Usually the database will contain some tables to keep > track of the current live version. > > With the state based approach you just store the DDL for each object in the > source control system. You can see how an object changes over time by just > inspecting one file. You can automate the scripting process or use one of > the diffing tools that supports comparing to DDL directly. > > State based handles merge conflicts better than migrations. Migrations > handles data changes better than state based. Migrations also is better if > you are deploying to multiple production databases that may all be on > different versions. > > If your database contains a lot of logic or you have a large distributed > team you are more likely to have merge issues and so state based is probably > the better choice. Smaller team, less logic and a production environment > where you need to be able to update from any version reliably? Migrations is > a good choice. > > Additionally you don't have to stick with one or the other. In the early > days while you have little data to worry about you might use the static > approach and then switch to migrations. You just pick a baseline to start > from and carry on from there. This is an excellent summary. I personally think the 'migrations' based approach (as you describe it) is a better approach for large teams and complex environments. A good migration script will redeploy functions and views from source without having to be instructed to do so by development. State migrations are good for simple cases, particularly when the level of database expertise on the team is low. A lot of times teams doing this tend to not even bother checking database scripts into SCM, a huge long term mistake IMO. It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's probably better to restore from backup anyways. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit)
Installing on Centos 6 PostgreSQL 9.5 (64 bit) ; ruby 1.8 and get from https://pkgs.org/centos-6/epel-x86_64/postgresql-plruby-0.5.3-4.el6.x86_64.rpm.html - plruby libraries(postgresql-plruby-0.5.3-4.el6.x86_64). Additional installed postgresql95-libs (postgresql95-libs-9.5.2-1PGDG.rhel6.x86_64) Trying add plruby to PostgreSQL - create function plruby_call_handler() returns language_handler as '/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so' language 'c'; The follows error appears - could not load library "/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so": /usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so: undefined symbol: FunctionCall3 Add libraries path to LD_LIBRARY+PATH environment variable, recreating ld .so.cache doesn't change result - the same error appears. What could be a problem!? Thanks in advance for any replies or tips how it could be done... Hanan Brener Database and Data Collection Team Leader Allot Communications
Re: [GENERAL] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit)
Hanan Brener writes: > Installing on Centos 6 PostgreSQL 9.5 (64 bit) ; ruby 1.8 > and get from > https://pkgs.org/centos-6/epel-x86_64/postgresql-plruby-0.5.3-4.el6.x86_64.rpm.html > - plruby libraries(postgresql-plruby-0.5.3-4.el6.x86_64). > Additional installed postgresql95-libs > (postgresql95-libs-9.5.2-1PGDG.rhel6.x86_64) > Trying add plruby to PostgreSQL - > create function plruby_call_handler() returns language_handler > as '/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so' > language 'c'; > The follows error appears - could not load library > "/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so": > /usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so: undefined symbol: > FunctionCall3 Seems like you've got a broken build of plruby there. FunctionCall3 has been a macro, not a real function, for quite a long time. AFAICS this error would only be possible if plruby had been compiled against postgres header files from 9.0 or before, which would be a packaging mistake. Suggest complaining to whoever the package builder is. regards, tom lane -- 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] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit)
On 06/30/2016 09:24 AM, Hanan Brener wrote: Installing on Centos 6 PostgreSQL 9.5 (64 bit) ; ruby 1.8 How did you install the Postgres server? and get from *https://pkgs.org/centos-6/epel-x86_64/postgresql-plruby-0.5.3-4.el6.x86_64.rpm.html* I would say where you install plruby from depends on where you installed Postgres from. FYI the Postgres community has repos: https://www.postgresql.org/download/linux/redhat/ - *_plruby_* libraries(postgresql-plruby-0.5.3-4.el6.x86_64). Additional installed postgresql95-libs (postgresql95-libs-9.5.2-1PGDG.rhel6.x86_64) Trying add plruby to PostgreSQL - create function plruby_call_handler() returns language_handler as '/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so' language 'c'; The follows error appears - could not load library "/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so": /usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so: undefined symbol: FunctionCall3 Add libraries path to LD_LIBRARY+PATH environment variable, recreating ld .so.cache doesn't change result – the same error appears. What could be a problem!? Thanks in advance for any replies or tips how it could be done... *Hanan Brener* *Database and Data Collection Team Leader* Allot Communications -- Adrian Klaver adrian.kla...@aklaver.com -- 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] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit)
The Postgresql server 9.5 installed from (www.enterprisedb.com/) Hanan Brener Database and Data Collection Team Leader Allot Communications -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Thursday, June 30, 2016 7:47 PM To: Hanan Brener ; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit) On 06/30/2016 09:24 AM, Hanan Brener wrote: > Installing on Centos 6 PostgreSQL 9.5 (64 bit) ; ruby 1.8 How did you install the Postgres server? > > and get from > *https://pkgs.org/centos-6/epel-x86_64/postgresql-plruby-0.5.3-4.el6.x > 86_64.rpm.html* I would say where you install plruby from depends on where you installed Postgres from. FYI the Postgres community has repos: https://www.postgresql.org/download/linux/redhat/ > - *_plruby_* libraries(postgresql-plruby-0.5.3-4.el6.x86_64). > > Additional installed postgresql95-libs > (postgresql95-libs-9.5.2-1PGDG.rhel6.x86_64) > > Trying add plruby to PostgreSQL - > > create function plruby_call_handler() returns language_handler > > as '/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so' > > language 'c'; > > The follows error appears - could not load library > "/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so": > /usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so: undefined symbol: > FunctionCall3 > > Add libraries path to LD_LIBRARY+PATH environment variable, recreating > ld .so.cache doesn't change result - the same error appears. > > > > What could be a problem!? > > Thanks in advance for any replies or tips how it could be done... > > *Hanan Brener* > > *Database and Data Collection Team Leader* > > Allot Communications > > > > > -- Adrian Klaver adrian.kla...@aklaver.com -- 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] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit)
On 06/30/2016 10:08 AM, Hanan Brener wrote: The Postgresql server 9.5 installed from (www.enterprisedb.com/) I don't know how you would do that. I saw(sort of) your post on the EDB forum: http://forums.enterprisedb.com/jforum.page?module=search&action=search&forum=3&match_type=all&search_keywords=ruby Clicking on the link yielded: "The topic you are trying to see does not exist." So you might want to try again there. Hanan Brener Database and Data Collection Team Leader Allot Communications -Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Thursday, June 30, 2016 7:47 PM To: Hanan Brener ; pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.5 and PL/Ruby install problem(Centos 6 64 bit) On 06/30/2016 09:24 AM, Hanan Brener wrote: Installing on Centos 6 PostgreSQL 9.5 (64 bit) ; ruby 1.8 How did you install the Postgres server? and get from *https://pkgs.org/centos-6/epel-x86_64/postgresql-plruby-0.5.3-4.el6.x 86_64.rpm.html* I would say where you install plruby from depends on where you installed Postgres from. FYI the Postgres community has repos: https://www.postgresql.org/download/linux/redhat/ - *_plruby_* libraries(postgresql-plruby-0.5.3-4.el6.x86_64). Additional installed postgresql95-libs (postgresql95-libs-9.5.2-1PGDG.rhel6.x86_64) Trying add plruby to PostgreSQL - create function plruby_call_handler() returns language_handler as '/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so' language 'c'; The follows error appears - could not load library "/usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so": /usr/lib64/ruby/site_ruby/1.8/x86_64-linux/plruby.so: undefined symbol: FunctionCall3 Add libraries path to LD_LIBRARY+PATH environment variable, recreating ld .so.cache doesn't change result - the same error appears. What could be a problem!? Thanks in advance for any replies or tips how it could be done... *Hanan Brener* *Database and Data Collection Team Leader* Allot Communications -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about "grant create on database" and pg_dump/pg_dumpall
Is it expected that "grant * on database" grants are dumped only by `pg_dumpall -s` and not by `pg_dump -s` or `pg_dumpall -g`? Some people might assume that to restore a cluster it should be sufficient to restore pg_dumpall globals output followed by individual pg_dump output. Seemingly, this would not be a good assumption, unless plain `pg_dump` actually incorporates these grants even though `pg_dump -s` does not. Regardless, something about this situation seems off to me. I'm using 9.5, BTW. I've seen this discussed here: https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org, but the discussion petered out prematurely. Thanks, Kevin
Re: [GENERAL] Log archiving failing. Seems to be wrong timeline
On Thu, Jun 30, 2016 at 3:53 AM, Chris Lewis wrote: > Hello, > > We have 2 postgresql servers (v 9.4.2) master and slave in streaming > replication. The overall cluster is controlled using pacemaker & corosync > and the pgsql cluster agent which handles failover to, and promotion of, the > slave. > > Recently a failover occured and I noticed that log archiving was failing on > the master: ... > > Why are we trying to archive logs which belong to an old timeline? Just because the timeline is old doesn't mean we want to destroy it. Afterall, the reason for having timelines in the first place is to preserve, not to destroy. It sounds like someone removed the old timeline's log files from pg_xlog, but did not remove the corresponding .ready files from pg_xlog/archive_status. If the old timeline's files are truly lost, then you will have to carefully remove those corresponding .ready files. Cheers, Jeff -- 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] Question about "grant create on database" and pg_dump/pg_dumpall
On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin wrote: > Is it expected that "grant * on database" grants are dumped only by > `pg_dumpall -s` and not by `pg_dump -s` or `pg_dumpall -g`? > > Some people might assume that to restore a cluster it should be sufficient > to restore pg_dumpall globals output followed by individual pg_dump output. > Seemingly, this would not be a good assumption, unless plain `pg_dump` > actually incorporates these grants even though `pg_dump -s` does not. > Regardless, something about this situation seems off to me. I'm using 9.5, > BTW. > > I've seen this discussed here: > https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org, > but the discussion petered out prematurely. > > I have to agree. At worse this is a documentation bug but I do think we have an actual oversight here - although probably not exactly this or the linked bug report. Testing this out a bit on 9.5 Ubuntu 14.04 - I believe the last command, , is in error. <
Re: [GENERAL] Replication with non-read-only standby.
On Thu, Jun 30, 2016 at 11:15 PM, Nick Babadzhanian wrote: > Setup: > 2 PostgreSQL servers are geographically spread. The first one is used for > an application that gathers data. It is connected to the second database > that is used to process the said data. Connection is not very stable nor is > it fast, so using Bidirectional replication is not an option. It is OK if > data is shipped in batches rather than streamed. > > Question: > Is there a way to make the standby server non-read-only, so that it can > keep getting updates (mostly inserts) from the 'master', but users are able > to edit the data stored on 'slave'? Is there some alternative solution to > this? > You can consider Ruby replication for such a requirement. I think, there is no much development happening around Ruby Replication since long time i believe. This can be used for production environment. http://www.rubyrep.org/ Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] table name size
Hi Is there any specific reason not releasing any version with "NAMEDATALEN 255" in file src/include/pg_config_manual.h ? this will really nice if we do with "NAMEDATALEN 255" src/include/pg_config_ manual.h Please Thanks Sridhar OpenText On Mon, Jun 13, 2016 at 5:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jun 13, 2016 at 7:21 AM, Sridhar N Bamandlapally < > sridhar@gmail.com> wrote: > >> Hi >> >> how to create table name with size, the limitation we are facing is 63 >> length >> >> these are dynamic tables created from application >> >> issue is: >> we cannot suggest/tell client to change NAMEDATALEN constant in >> src/include/pg_config_manual.h >> >> do we have any other option, >> >> > Ensure that the application's algorithm for generating names doesn't > generate names that exceed 63 characters. > > If you're asking if there a runtime setting to control this the answer is > no. > > David J. > >
