Re: [GENERAL] Postgresql and github
Dear friends, The reason I posted this: For the last 6 months I have noticed the enormous numbers of github's raising on the Internet. For the last 10 years I have worked with Oracle(yes expensive)and Postgresql(from version 8.4!) and I am familiar conserning the _big_ difference between the to database environments. Good and not so good. Administration well Oracle is easier because of the instance/backup/restore(point in time) crontrolled by itselr. Postgresql does not have that kind of stuff - I mean backup/restore(pointin time controlled by _itself_. But it is free and free of charge. But is not 24/7 when upgrading. Therefore: To make administration easier for your customers I suggest to establish a github with different possibilites, showing how to upgrade minor/major in 24/7 enviroments especially the restore(point in time) as a "build in" method liek Oracle'srman. I have been told by a consultant that pgBackRest is recommended. But it is not buildin in the way Oracle's rman. Sorry if i got out of the scope of my mail. And sorry for my demands.:) BTW: why are PG databases visible by all users (prompt: \l) Thanks Poul 2017-11-09 19:00 GMT+01:00 Steve Atkins <st...@blighty.com>: > > > On Nov 9, 2017, at 9:37 AM, Poul Kristensen <bcc5...@gmail.com> wrote: > > > > No it isn't. > > > > What I want to do is: > > > > ansible-playbook somepostgresql.yml > > > > and postgresql is then changed on some server > > the way things are done by e.g. > > github.com/oravirt/ansible-oracle > > You're looking for help with an Ansible recipe, not with anything to do > with PostgreSQL itself. > > Mentioning it here, in case someone already has one, is worth a try but > you're likely going to need to go talk to the Ansible people. Or write your > own. > > Cheers, > Steve > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Med venlig hilsen / Best regards Poul Kristensen Linux-OS/Virtualizationexpert and Oracle DBA
Re: [GENERAL] Postgresql and github
No it isn't. What I want to do is: ansible-playbook somepostgresql.yml and postgresql is then changed on some server the way things are done by e.g. github.com/oravirt/ansible-oracle Thanks Poul 2017-11-09 17:18 GMT+01:00 Igal @ Lucee.org <i...@lucee.org>: > On 11/9/2017 3:27 AM, Poul Kristensen wrote: > > How come that Postgresql is not present in a github with latest release? > > > What do you mean? Is this not what you're referring to: > https://github.com/postgres/postgres/releases > ? > > Igal Sapir > Lucee Core Developer > Lucee.org <http://lucee.org/> > -- Med venlig hilsen / Best regards Poul Kristensen Linux-OS/Virtualizationexpert and Oracle DBA
[GENERAL] Postgresql and github
Hi! How come that Postgresql is not present in a github with latest release? It would be very convenient to deploy PG using Ansible. Oracle(latest release) is available through github. TIA Poul
Re: [GENERAL] dynamic schema modeling and performance
dataverse.org uses Postgresql and is well documented + it is completely user driven. Maybe the concept could be usefull for you. I have installed and configuration a few to be uses for researchers. regards Poul 2017-04-11 19:46 GMT+02:00 Rj Ewing <ewing...@gmail.com>: > I'm looking for thoughts on the best way to handle dynamic schemas. > > The application I am developing revolves around user defined entities. > Each entity is a tabular dataset with user defined columns and data types. > Entities can also be related to each other through Parent-Child > relationships. Some entities will be 100% user driven, while others (such > as an entity representing a photo) will be partially user driven (all photo > entities will have common fields + custom user additions). > > I was hoping to get opinions on whether postgresql would be a suitable > backend. A couple of options I have thought of are: > > 1. Each entity is represented as a table in psql. The schema would be > dynamically updated (with limits) when an entity mapping is updated. I > believe that this would provide the best data constraints and allow the > best data normalization. *A concern I have is that there could be an > enormous amount of tables generated and the performance impacts this might > have in the future*. I could then run elasticsearch as a denormalized > cache for efficient querying and full-text-search. > > 2. Use a nosql database. This provides the "dynamic" schema aspect. A > concern here is the lack of relation support, thus leading to a more > denormalized data structure and the potential for the data to become > corrupted. > > Any opinions on the use of psql for this case, or other options would be > greatly appreciated! > > RJ > -- Med venlig hilsen / Best regards Poul Kristensen Linux-OS/Virtualizationexpert and Oracle DBA
[GENERAL] Checksum and Wal files
Hi! Are all files inclusive wal files added a checksum? We will be doing regularly tests of recovery using checksums as the parameter of a succesfull recovery. Thanks Poul
[GENERAL] pg_hba.conf debugging or logging when using ldag to authenticate
Hi ! Does anyone know how to log or debug authentication against ad? A few years ago is it was possible to log everything to confirm using the right KDC and the right principal and hereby be sure to send the right userid possible concatenated with the realm.(I can't remember exacty) As far as I can see this is not possible anymore. When using ldapsearch everything works fine.But the ldap authentication does not help much as the pg_log is just responting thd failure of credentials. Changing password using Kerberos works fine(does this use the keytab or is the KDC issuing a new ticket). The documented examples is used using cn=gssapi, cn=auth Is it possible to use cached ticket in the keytab option in postgresql.conf when enabling the use of gssapi. Sorry for a lot of questions but I thing there is a lack logs/debugging facilities now. 4-5 years ago it was no problem. Thanks Poul
Re: [GENERAL] ora2pg - Java Message Service (JMS) Type
The R statistic software is using PostgreSQL. Oracle has a module to transform PostgreSQL tables very easy both ways. Just install R and the module. I can't remember the name of the module but try googling. Hope it helps. /Poul 2016-12-09 18:22 GMT+01:00 Joanna Xu <joanna...@amdocs.com>: > Hi All, > > > > We plan to use ora2pg tool to migrate Oracle to PostgreSQL. During the > testing, while importing content of the output.sql into PostgrSQL database, > it terminated with the following error “ERROR: type "aq$_jms_text_message" > does not exist” for “user_data” column with “AQ$_JMS_TEXT_MESSAGE” as > type. In this case, if Java Message Service (JMS) types are not supported > by Postgres, ora2pg would not be the right tool to use for the migration > but I am not certain if it is true. Can someone please advise? > > > > psql -d wsp -U staging -W < /database/postgres/outputSTAGING.sql > > Password for user staging: > > SET > > CREATE TABLE > > : > > CREATE TABLE > > ALTER TABLE > > CREATE INDEX > > ERROR: type "aq$_jms_text_message" does not exist > > LINE 29: user_data AQ$_JMS_TEXT_MESSAGE, > > > > Thanks, > > Joanna > This message and the information contained herein is proprietary and > confidential and subject to the Amdocs policy statement, you may review at > http://www.amdocs.com/email_disclaimer.asp > -- Med venlig hilsen / Best regards Poul Kristensen Linux-OS/Virtualizationexpert and Oracle DBA
Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used
Solution:
C is able to compile with an embedded shell script.
So a small C program with an argument being the user passed to the shell
script it succeded succesfully.
I am aware that it could be my question was not specifically relevant to
Postgresql! :)
It took a while to solve. :)
Thanks
Poul
2016-11-23 21:31 GMT+01:00 Poul Kristensen <bcc5...@gmail.com>:
> My goal is to grap a user from the OS and and substitute the grapped user
> - to be the owner of the database - in the sql commands
> e.g using shell-script substitution syntax:
>create user ${user} with login;
>create tablespace ${user} location '/path/${user)';
>create database ${user} owner ${user} on tablespace ${user};
> I would be surpriced if this should not be possible using gnu gcc but I
> might be wrong. It is just a question of figurering out way to do this.
> http://zetcode.com/db/postgresqlc/ has an example of how to do this using
> integer as argument on
> the commandline.
> Do you have a hint of the substitution syntax to be used in gnu gcc.
>
> Thanks.
>
> /Poul
>
>
>
> 2016-11-23 16:44 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>:
>
>> "David G. Johnston" <david.g.johns...@gmail.com> writes:
>> > On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5...@gmail.com>
>> wrote:
>> >> In the postgresql.log I get "select datname from $1".
>>
>> > That is expected when you parameterize a query - the query string
>> includes
>> > parameters instead of values.
>>
>> It looks like Poul is hoping to use a parameter as a table name,
>> which doesn't work. Parameters can only represent values (constants).
>>
>> regards, tom lane
>>
>
>
>
> --
> Med venlig hilsen / Best regards
> Poul Kristensen
> Linux-OS/Virtualizationexpert and Oracle DBA
>
--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA
Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used
My goal is to grap a user from the OS and and substitute the grapped user -
to be the owner of the database - in the sql commands
e.g using shell-script substitution syntax:
create user ${user} with login;
create tablespace ${user} location '/path/${user)';
create database ${user} owner ${user} on tablespace ${user};
I would be surpriced if this should not be possible using gnu gcc but I
might be wrong. It is just a question of figurering out way to do this.
http://zetcode.com/db/postgresqlc/ has an example of how to do this using
integer as argument on
the commandline.
Do you have a hint of the substitution syntax to be used in gnu gcc.
Thanks.
/Poul
2016-11-23 16:44 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>:
> "David G. Johnston" <david.g.johns...@gmail.com> writes:
> > On Wed, Nov 23, 2016 at 3:46 AM, Poul Kristensen <bcc5...@gmail.com>
> wrote:
> >> In the postgresql.log I get "select datname from $1".
>
> > That is expected when you parameterize a query - the query string
> includes
> > parameters instead of values.
>
> It looks like Poul is hoping to use a parameter as a table name,
> which doesn't work. Parameters can only represent values (constants).
>
> regards, tom lane
>
--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA
Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used
The below mentioned pg_database does not appear in $1.
In the postgresql.log I get "select datname from $1".
Do you have a suggestion of how to solve this?
I get double declarations when using both
char *paramValues[1]
char *paramValues[1]
//char *value1 = "pg_database";
//char **paramValues = /* closer match to the documentation's
syntax */
char *paramValues[1] = {"pg_database"}; /* same as above, different syntax
*/
//
//char *paramValues[1]; /* this looks to be missing */
//paramValues[0]={"pg_database"}; /* what you had */
res = PQexecParams(conn,
"select datname from $1",
1, /* there is only 1 entry in paramValues array */
NULL,
paramValues, /* address of parameter value array */
NULL, /* don't need param lengths since text */
NULL, /* defaul to all text params */
1); /* return all values as binary */
Thanks.
/Poul
2016-11-22 15:50 GMT+01:00 John McKown <john.archie.mck...@gmail.com>:
> On Tue, Nov 22, 2016 at 8:22 AM, Poul Kristensen <bcc5...@gmail.com>
> wrote:
>
>> I think I understand.
>> When I use this in my code I get
>> "undefined reference to `PQexecParms'
>>
>
> The correct name is PQexecParams (note the last "a"). Sorry I missed that
> when first looking. Also, just to be sure, did you include the argument
> "-lpq" on the compile command to point to the PostgreSQL library for
> linking?
>
>
>
>> when compiling.
>>
>> references in main is
>>
>> const char *conninfo; /* connection string to the database */
>> PGconn *conn; /* connection to the database */
>> PGresult *res; /* result of sql query */
>> int nFields; /* print out the attribute names */
>> int i; / * print the columns */
>> j;
>>
>> Is the a reserved reference to use with
>>
>> Reserved res = PQexecParms(conn )
>>
>> Then I assume that I have to use another reference than res.
>>
>> Thanks.
>>
>> /Poul
>>
>>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
>
--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA
Re: [GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used
I think I understand.
When I use this in my code I get
"undefined reference to `PQexecParms'
when compiling.
references in main is
const char *conninfo; /* connection string to the database */
PGconn *conn; /* connection to the database */
PGresult *res; /* result of sql query */
int nFields; /* print out the attribute names */
int i; / * print the columns */
j;
Is the a reserved reference to use with
Reserved res = PQexecParms(conn )
Then I assume that I have to use another reference than res.
Thanks.
/Poul
2016-11-22 0:48 GMT+01:00 John McKown <john.archie.mck...@gmail.com>:
> On Mon, Nov 21, 2016 at 11:22 AM, Poul Kristensen <bcc5...@gmail.com>
> wrote:
>
>> Thank you for fast repons!
>>
>> The $1 substitution below. I assume that it refers to "joe's place". But
>> it is not very clear to me, how "joe's place" will appear instead of $1
>> when running. Where is it possiible to read more about this? There just
>> is'nt much about substitution in C online. Any recommended books to buy?
>>
>>
>> /* Here is our out-of-line parameter value */
>> paramValues[0] = "joe's place";
>>
>> res = PQexecParams(conn,
>>"SELECT * FROM test1 WHERE t = $1",
>>1, /* one param */
>>NULL,/* let the backend deduce param type */
>>paramValues,
>>NULL,/* don't need param lengths since text */
>>NULL,/* default to all text params */
>>1); /* ask for binary results */
>> }
>>
>> /Poul
>>
>>
>>
> It is described better here: https://www.postgresql.org/
> docs/9.6/static/libpq-exec.html
> than I can do. But I just noticed a mistake in your code, or maybe just
> something left out. I would say:
>
> char *value1 = "joe's place";
>
> char **paramV
> a
> lues =
> /* closer match to the documentation's syntax */
>
> //char *paramValues[] = {"joe's place"}; /* same as above, different
> syntax */
> //
> //char *paramValues[1]; /* this looks to be missing */
> //paramValues[0]="joe's place"; /* what you had */
> res = PQexecParms(conn,
>"SELECT * FROM test1 WHERE t = $1",
>1, /* there is only 1 entry in paramValues array */
>paramValues, /* address of parameter value array */
>NULL, /* don't need param lengths since text */
>NULL, /* defaul to all text params */
>1); /* return all values as binary */
>
> Well, you have an array of pointers to characters called paramValues. The
> $1 refers to whatever is pointed to by paramValues[0], which is a pointer
> to value1 which is a C "string". Basically in the second parameter, the
> command, the $n is used as a 1-based index into the paramValues[] array.
> This means that the actual C language array value is one less (since C
> arrays are 0-based). Which means that "$n" (n>=1) in the "command" string
> refers to value pointed to by paramValues[n-1]. The 3rd value, 1 in this
> case, tells PQexecParms how many entries there are in the paramValues[]
> array. I guess this is a type of validity check that the $n in the command
> string is not too large for the array.
>
> Note: please keep the discussion on the list, not to me personally. It may
> be of help to others (or maybe not, I don't know.)
>
> --
> Heisenberg may have been here.
>
> Unicode: http://xkcd.com/1726/
>
> Maranatha! <><
> John McKown
>
--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA
[GENERAL] Postgresql 9.5 and Shell scripts/variables vs. C programming/defining a value to be used
Hi!
Hopefully this is this list.
A shell variabel is defined like this
var1= value
used like ${var1}.
How is the equal defened in the Postgresql C programming?
Thanks.
Poul
BTW: I have not received my ordered book yet!
[GENERAL] C code with embedded SQL
Can't the gcc compiler be used for the subject I have ordered the book "Postgresql for develophers" and hope to see some examples there covering C(Gnu) with embedded SQL. Thanks Poul
