[GENERAL] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)
Hi, I'm trying to import some data from a MS SQL Server 2014 sequential database to Postgresql using dbi-link. Postgresql 9.6 encoding is utf-8 and does not support utf-16 but, I need to transfer this data to postgres. I also tried to use tds_fdw version 1.0.8 and the same problem occurs. ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to 7.1!, OS #: 0, OS Msg: Success, Level: 1 ** Error ** ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to 7.1!, OS #: 0, OS Msg: Success, Level: 1 SQL state: HV00L Please help me.
Re: [GENERAL] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16)
I updated odbc drivers and it worked. Thanks everyone Regards, Juliano Sent with [ProtonMail](https://protonmail.com) Secure Email. Original Message Subject: Re: [GENERAL] Import data from MS SQL Server 2014 to Postgresql 9.6 using dbi-link and fdw (error: utf-8/uft-16) Local Time: November 10, 2016 1:31 PM UTC Time: November 10, 2016 1:31 PM From: floripa...@gmail.com To: Juliano , pgsql-general@postgresql.org On Thu, 2016-11-10 at 05:34 -0500, Juliano wrote: > Hi, > > I'm trying to import some data from a MS SQL Server 2014 sequential > database to Postgresql using dbi-link. > > Postgresql 9.6 encoding is utf-8 and does not support utf-16 but, I > need to transfer this data to postgres. > > I also tried to use tds_fdw version 1.0.8 and the same problem > occurs. > > ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to > 7.1!, OS #: 0, OS Msg: Success, Level: 1 > > ** Error ** > > ERROR: DB-Library error: DB #: 100, DB Msg: TDS version downgraded to > 7.1!, OS #: 0, OS Msg: Success, Level: 1 > SQL state: HV00L > > Please help me. > The error code HV00L means "unable to create execution". This appears to have been fixed. See:- https://github.com/tds-fdw/tds_fdw/issues/83 However, I don't follow "how" the UTF-16 data is being transformed to UTF-8. I know that I'm thick (at times). If the SQL Server data is in Simplified Chinese (say), how do you convert it? Postgres handles some multi-byte codings:- WIN932 --> SJIS WIN936 --> GBK WIN949 --> UHC WIN950 --> BIG5 So you can run createdb specifying one of those encodings, lc_collate, etc. HTH, Rob
[GENERAL] pg_dump system catalog
Hi everyone, How can I use pg_dump to do a backup of "system catalogs" only? Regards, Juliano
Re: [GENERAL] pg_dump system catalog
I'm not knew that system catalogs could be saved as schema, thanks Achilleas. So, I would like to get the data and the schema of the system catalogs. How can I include the data in a dump file with system schemas? (The parameter -a makes sense together the parameter --schema?) Regards, Juliano
[GENERAL] Overwrite pg_catalog?
Hi everyone, I tried to restore pg_catalog to my new database, but the existing pg_catalog can't be overwritten or dropped, and postgres auto creates pg_catalog when I create a new DB. So, there is a way to restore the pg_catalog to a new database? Regards, Juliano
Re: [GENERAL] Overwrite pg_catalog?
Hi everyone, Ok, thanks. Just to explain: I have some custom objects on my pg_catalog and I would like to restore it in a new test database and I think that this is the best way, isn't it? Regards, Juliano Original Message Subject: Re: [GENERAL] Overwrite pg_catalog? Local Time: December 2, 2016 2:18 AM UTC Time: December 2, 2016 2:18 AM From: michael.paqu...@gmail.com To: Francisco Olarte Juliano , pgsql-general@postgresql.org On Thu, Dec 01, 2016 at 01:07:09PM +0100, Francisco Olarte wrote: > Juliano: > > On Thu, Dec 1, 2016 at 12:16 PM, Juliano wrote: > > I tried to restore pg_catalog to my new database, but the existing > > pg_catalog can't be overwritten or dropped, and postgres auto creates > > pg_catalog when I create a new DB. > > This is because, in general, pg_catalog is maintained by DML > statements, restoring it is not going to do what you think. s/DML/DDL/ -- Michael
[GENERAL] repmgr new standby node without clone master data
Hi guys, I'd like to insert a new standby node without clone the data of the master, how is it possible? Explain: Register a new slave node on the cluster and avoid copy 2TB of data by network, so it comes with the clone data on the disk. After this I think just increment the new data with --rsync-only. Regards Juliano
[GENERAL] repmgr cascade replication node delay
Hi guys, I have a replication scenario master/slave and I would like to create one extra more node (cascade from slave) with a time delay of 24-hour replication. The objective of an extra node 24 hours delayed is to recover some error in the server. Is it possible to make this configuration? Open to suggestions, please ignore pg_dump and barman. Regards Juliano
Re: [GENERAL] repmgr cascade replication node delay
Hi David, Thanks for your email. This recovery_min_apply_delay sounds interesting! I wold like to use it between 2 standby nodes (cascade replication using repmgr) instead of master/standby, anyone knows if is it possible? Regards, Juliano Sent with [ProtonMail](https://protonmail.com) Secure Email. Original Message Subject: Re: [GENERAL] repmgr cascade replication node delay Local Time: June 1, 2017 11:42 PM UTC Time: June 1, 2017 10:42 PM From: david.g.johns...@gmail.com To: Juliano pgsql-general@postgresql.org , pgsql-general-ow...@postgresql.org On Thu, Jun 1, 2017 at 3:30 PM, Juliano wrote: The objective of an extra node 24 hours delayed is to recover some error in the server. Is it possible to make this configuration? A quick search turns up: recovery_min_apply_delay https://www.postgresql.org/docs/current/static/standby-settings.html No personal experience though. David J.
[GENERAL] Cluster, repmgr, pgbouncer, pgpool, ha proxy, virtual IP, replication, failover and load balance
Hi guys > I would like to deploy load balance on Postgres master/slave(RW/R) servers > and also use a virtual IP on this cluster > > I currently have Postgres 9.4 using Repmgr for replication and manual > failover to avoid split-brain problems and save a witness server. Also, there > is Pgbouncer connection pooling working in session mode > > So, what is recommended to use in my current environment? > > Is Pgpool the best solution to use with Repmgr+Pgbouncer? In this case, > should I replace Pgbouncer for Pgpool or use both? What about HA proxy? > > I have never configured a virtual IP to cluster, should I use Keepalived, > Heartbeat or any other solution? > > Here is the image of what is in my mind: https://ibb.co/fZFebk > [cluster.jpg] > > Thanks in advance for any help!
[GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)
Hi guys I've been struggling for long years to find an alternative Postgres Manager that can support pgAgent jobs like pgAdmin does. PgAdmin works fine, but, it is not good as Oracle Enterprise Manager or SQL Server Management Studio and there is a lot of bugs. The version 1 it is obsolete and the new version 1.6/2 sucks. I have tried Dbeaver but apparently, support jobs for Oracle only. Omnidb looks nice, but, I guess doesn't support pgAgent as well, any suggestions?
Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)
Thanks to everyone for your great answers I have looked into pg_cron, jpgAgent and pgAutomator and enjoyed (j)pgAgent + pgAdmin so far pg_cron looks great but rewrite my 346 jobs seems painful Adam your tool is really nice and I would be glad to help on this project, btw I am not a developer but I would like to start to code as well Regards Juliano > Original Message > Subject: Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support > pgagent (jobs) > Local Time: 20 October 2017 4:14 AM > UTC Time: 20 October 2017 03:14 > From: adambrusselb...@gmail.com > To: pgsql-general@postgresql.org > > I'm currently in the same boat that I wish there was something better > for running jobs against Postgres than pgAgent. > Using pgAdmin to manage my numerous jobs isn't the best experience > i've ever had to say the least, but it does work. No other tool I > have used will manage pgAgent jobs. > > I worked to alleviate some of my pain with pgAgent, by re-writing the > entire agent portion: https://github.com/GoSimpleLLC/jpgAgent > It uses the same schema as pgAgent, and you can manage it with pgAdmin > just the same, it just has way more features, and has been extremely > stable for me. > > One thing I have wanted to do for a while, is to write an entire > alternative job scheduler / management tool separate from pgAdmin > which will be dedicated to just that single purpose. > I put in a good bit of work on that, but haven't had time to go back > and get it to a point where I can actually release it: > https://github.com/Tostino/pgAutomator > The goal is to have a tool much closer to what is available for SQL > Server, or Oracle compared to what we have today. The backend stuff > is mostly working, I just need to find time to write a frontend to > manage it. > > Anyways, sorry for a bit of rambling. My main point was that this is > something that has been a personal pain point, and I haven't found > anything I liked better up to this point in time. > I'm slowly working on remedying that, but until the time my > alternative is ready, i'm sticking with (j)pgAgent, and pgAdmin to > manage it. > > Thanks, > -Adam > > 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] Alternative to pgAdmin Postgres Manager that support pgagent (jobs)
Great! No worries, after work I will start with some surveys about how to package Postgres extension, and get it onto pgxn. Thanks Juliano Original Message > Subject: Re: [GENERAL] Alternative to pgAdmin Postgres Manager that support > pgagent (jobs) > Local Time: October 21, 2017 9:18 PM > UTC Time: October 21, 2017 8:18 PM > From: adambrusselb...@gmail.com > To: Juliano > pgsql-general@postgresql.org > > Happy to hear jpgAgent is working alright for you. If you have any > questions with it feel free to ask me. > > If you do want to help with pgAutomator, that sounds like something > you could start to learn on. jpgAgent is pretty much feature complete > as far as my needs go, and no one has requested any additional > features, so i'd rather spend any new time on pgAutomator. > > So one thing I know I need, is to figure out how to package a Postgres > extension, and get it onto pgxn. The database portion of pgAutomator > is pretty complete at this point, so it'd be nice to learn how to > package it up even if I don't end up publishing it until I get a UI in > place. I'll have to look, but i'm sure there are plenty of small > tasks that can be done with the agent itself as well.
[GENERAL] Experiences with BLOB + PostgreSQL
Hello, I'd like to ask you about some experience in managing huge databases which store mostly binary files. We're developing a system which is likely to grow up to terabytes in some years and I'd like to hear something from people who really administrate these kinds of databases. Please tell us the size of you database and the main problems you encountered managing it. It will certainly be very helpful, specially for me. Thank you all! Juliano -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regression Tests
Hi, I've installed PostgreSQL 7.0.2 on Solaris following the INSTALL file that comes with the source. When I do: make runtest it gives out the following error message in regress.out: postmaster must already be running for the regression tests to succeed. The time zone is set to PST8PDT for these tests by the client frontend. Please report any apparent problems to [EMAIL PROTECTED] See regress/README for more information. === dropping old regression database... = DROP DATABASE === creating new regression database... = CREATE DATABASE === installing languages... = installing PL/pgSQL .. createlang: missing required argument PGLIB directory (This is the directory where the interpreter for the procedural language is stored. Traditionally, these are installed in whatever 'lib' directory was specified at configure time.) failed Also, when I do: make runcheck the following message is in the postmaster.log IpcSemaphoreCreate: semget failed (No space left on device) key=65432015, num=16, permission=600 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. FATAL 1: InitProcGlobal: IpcSemaphoreCreate failed
[GENERAL] Insert with query
By doing insert into a table using a query, it seems that all records of consultation were included bypassing the AFTER INSERT triggers and as few as after all the records already included, the TRIGGER is fired for each record, I wonder if this occurrence is normal. I created an example that demonstrates my doubt, where the 1st case works properly. The 2nd case is restricted by TRIGGER when I think, that could not be EXEMPLE drop table if exists test; create table test(chave serial primary key, campo1 integer, campo2 integer, campo3 integer); CREATE OR REPLACE FUNCTION ft_block() RETURNS trigger AS$BODY$declare vMax integer;begin select max(coalesce(campo3, campo2)) from test where campo1 = new.campo1 into vMax; if vMax > coalesce(new.campo3, new.campo2) then raise 'Erro na inclusão: %, %, %, %', new.campo1, new.campo2, new.campo3, vMax; end if; return null;end;$BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER t_block AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE ft_block(); create or replace function f_insert_in_test()returns boolean as$BODY$declare r record;begin for r in (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x) loopinsert into test(campo1, campo2, campo3) values(r.column1, r.column2, r.column3); end loop; return true;end;$BODY$language plpgsql; --- 1º CASE -- OKdelete from test;select f_insert_in_test(); --- 2º CASE --- BLOCKdelete from test;insert into test(campo1, campo2, campo3) (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x); select * from test;
