On Thu, Nov 21, 2019 at 05:13:31PM +0100, Laurenz Albe wrote:
> How should that work for tables other than temporary tables?
> Should COMMIT lock if somebody else accesses the table?
Postgres does not support read uncommitted, so the table would not be
visible to other sessions until the
čt 21. 11. 2019 v 22:04 odesílatel Michael Korbakov
napsal:
> On November 21, 2019 at 19:14:33, Pavel Stehule (pavel.steh...@gmail.com)
> wrote:
>
>
>
> čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov
> napsal:
>
>> Hi everybody.
>>
>> I stumbled upon a weird problem with the query planner.
Thanks Imre, this is a very important comment, 128 bits is much smaller than
45*8+2=362.
Very glad to know that, thank you very much!
James
--Original--
From: "Imre Samu"https://www.postgresql.org/docs/11/datatype-uuid.html
"UUID would be the fastest
Oh okay! I wasn't aware of the pg_settings system view. Thanks for all
the info!
On Thu, Nov 21, 2019 at 1:36 PM Joe Conway wrote:
> On 11/21/19 1:27 PM, Dave Hughes wrote:
> > Thank you so much for all your help! I found out my issue on accident
> > actually. I backed up all my user
On 2019-11-21 07:21:10 -0800, Adrian Klaver wrote:
> On 11/21/19 6:51 AM, Laurenz Albe wrote:
> > - The different databases in a cluster are physically located in
> >the same tablespace, but they are logically strictly separated.
> >You cannot connect to one database and access another
On 2019-11-21 08:18:21 -0600, Ron wrote:
> It appears to me that, within this one Postgres "instance", there are 2
> levels of "isolation", which are database, and schemas. Is this correct?
[...]
> If so, how does this cores pond to physical on disk storage?
>
> It corresponds not at
On 11/21/19 1:52 PM, stan wrote:
On Thu, Nov 21, 2019 at 12:14:16PM -0800, Adrian Klaver wrote:
5) Now in your case you have peer auth(first in the list) for local socket
connections which means a user can only connect on the local socket as the
db postgres user if they are also the os
On 11/21/19 1:14 PM, Jason L. Amerson wrote:
1) I have attached a screenshot of the output of "ps ax | grep post" on the
Ubuntu machine.
2) Since I was new to PostgreSQL, I followed a tutorial online. I did install
from source which I already knew how to do. I got the source package from
On Thu, Nov 21, 2019 at 12:14:16PM -0800, Adrian Klaver wrote:
> On 11/21/19 11:09 AM, stan wrote:
> >
>
> > > It would help if you could spell out what you want to achieve, as I am
> > > sure
> > > it has been done before by multiple people on this list.
> > >
> >
> > Can do.
> >
> > At
On 2019-11-21 16:48:14 +, Geoff Winkless wrote:
> On Thu, 21 Nov 2019 at 15:32, Peter J. Holzer wrote:
> > On 2019-11-21 09:43:26 +, Geoff Winkless wrote:
> > > It wasn't meant to be insulting, I meant "esoteric" in the strict
> > > sense: that you need to have specific knowledge to parse
On 2019-11-21 17:27:04 +0100, Moreno Andreo wrote:
> Connection refused means somthing has blocked it. If it was all OK and simply
> Postgres was not listening, you should've received a "connection timed out"
> (10060) message.
Almost exactly the other way around. If you try to connect to a port
On 11/21/19 1:14 PM, Jason L. Amerson wrote:
1) I have attached a screenshot of the output of "ps ax | grep post" on the
Ubuntu machine.
What program are you using to SSH into the remote machine?
It should allow you to copy 'n' paste the screen output without
resorting to screenshots. Text
change your listen_on setting to '*'
and add an entry to your hosts file for your machine.something like this:
192.168.1.10 thuban.mydomain thuban
Replace 192.168.1.10 with your ip address.
On Thu, Nov 21, 2019 at 3:14 PM Jason L. Amerson
wrote:
> 1) I have attached a screenshot of the output
"Jason L. Amerson" writes:
> pg_settings show localhost.
What I asked you about was the "source" columns.
regards, tom lane
Is your hosts file configured correctly? Is there an entry for the IP
address you are using in your hosts file? And does it point to the correct
hostname and ip address?
On Thu, Nov 21, 2019 at 2:07 PM Jason L. Amerson
wrote:
> pg_settings show localhost.
>
> Jason L. Amerson
>
>
>
On 11/21/19 12:07 PM, Jason L. Amerson wrote:
pg_settings show localhost.
Lets back up a bit and:
1) Show output of ps ax | grep post on Ubuntu machine.
2) Explain how you installed Postgres, source, package and if
package from what repo?
Jason L. Amerson
-Original Message-
On 11/21/19 11:09 AM, stan wrote:
It would help if you could spell out what you want to achieve, as I am sure
it has been done before by multiple people on this list.
Can do.
At the moment, I have 2 instances one production and one sandbox. I want to
be able to run pg_dump -d
pg_settings show localhost.
Jason L. Amerson
-Original Message-
From: Tom Lane
Sent: Thursday, November 21, 2019 02:42 PM
To: Jason L. Amerson
Cc: 'Steve Crawford' ; 'Adrian Klaver'
; 'PostgreSQL'
Subject: Re: Remote Connection Help
"Jason L. Amerson" writes:
> Yes
pg_settings still show localhost. I went back and added the line that
someone suggested to my "pg_hba" file so the end of mine now looks like
this:
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
When I run "netstat -nlt | grep 5432", I still only get "tcp
127.0.0.1:5432." As I mentioned
Mark Johnson writes:
> As I recall, if the listening address is set to '*' but is showing
> localhost, then the problem you describe is likely due to missing an IPv6
> address in pg_hba.conf.
No, the contents of pg_hba.conf don't directly impact the listen_addresses
setting. Also, if that's
I went back and added the line you suggested to my “pg_hba” file so the end of
mine now looks like this:
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
When I run “netstat -nlt | grep 5432”, I still only get “tcp 127.0.0.1:5432.”
As I mentioned before, I also see "127.0.0.1" on
"Jason L. Amerson" writes:
> Yes "listen_addresses" is not commented. I did notice when I did the netstat,
> for tcp, it was all "127.0.0.1" on various ports including 5432 but I have a
> listing for tcp6 that has my static IP using port 32305. Would that make a
> difference?
Hm, well,
On Thu, Nov 21, 2019 at 08:18:08AM -0800, Adrian Klaver wrote:
> On 11/21/19 8:12 AM, stan wrote:
> Please reply to list also
> Ccing list.
> > On Thu, Nov 21, 2019 at 07:56:10AM -0800, Adrian Klaver wrote:
> > > On 11/21/19 6:35 AM, stan wrote:
>
> > > > If the 1st rule matches, I am thinking
As I recall, if the listening address is set to '*' but is showing
localhost, then the problem you describe is likely due to missing an IPv6
address in pg_hba.conf. For me, I just added a line to pg_hba.conf like
this:
hostall all ::1/128 md5
So, even
Yes "listen_addresses" is not commented. I did notice when I did the netstat,
for tcp, it was all "127.0.0.1" on various ports including 5432 but I have a
listing for tcp6 that has my static IP using port 32305. Would that make a
difference?
Jason L. Amerson
-Original Message-
From:
On 11/21/19 1:27 PM, Dave Hughes wrote:
> Thank you so much for all your help! I found out my issue on accident
> actually. I backed up all my user accounts into a SQL scripts and after
> reviewing it, I noticed there were some lines that said:
> ALTER ROLE postgres SET "pgauid.log" to 'Role';
>
Thank you so much for all your help! I found out my issue on accident
actually. I backed up all my user accounts into a SQL scripts and after
reviewing it, I noticed there were some lines that said:
ALTER ROLE postgres SET "pgauid.log" to 'Role';
ALTER ROLE postgres SET "pgaudit.log_level" to
"Jason L. Amerson" writes:
> I connected to PostgreSQL locally. I ran “show listen_addresses;” and it
> returned “localhost.” I ran “show port;” and it returned “5432.” I am now
> confused. I edited the “postgresql.conf” file and change the setting to ‘*’.
> Then I restarted the server with
I connected to PostgreSQL locally. I ran “show listen_addresses;” and it
returned “localhost.” I ran “show port;” and it returned “5432.” I am now
confused. I edited the “postgresql.conf” file and change the setting to ‘*’.
Then I restarted the server with “service postgresql restart.” I was in
čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov
napsal:
> Hi everybody.
>
> I stumbled upon a weird problem with the query planner. I have a query
> on a typical EAV schema:
>
> SELECT contacts.id
> FROM contacts
> LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS
>
psql is the client, not the server. What user you run psql as doesn’t make a
difference, it’s what user the server is running as that makes the difference,
since it is the server that interacts with the file system. psql simply
connects to and interacts with the PostgreSQL server.
---
Israel
On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson
wrote:
> 1) I am not sure if Postgres server is listening on port 5432. How do I
> check?
>
> 2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U
> postgres."
> I even tried to telnet to it using the static IP and port 5432 but it
> On Nov 21, 2019, at 9:35 AM, Marc Millas wrote:
>
> Hi Laurenz,
>
>
> I was writing select from ""table"" as a template. We have to do this for a
> bunch of tables.
> So, to my understanding, what you suggest is to PITR up to the first
> timestamp, extract all meaningfull tables, and then
If you just removed the iptables rules, then every port is blocked. If you
turned off iptables (service iptables stop, chkconfig iptables off,) then
iptables is disabled.
On Thu, Nov 21, 2019 at 10:50 AM Jason L. Amerson
wrote:
> I have removed the rules in the iptables and restarted it and
I have removed the rules in the iptables and restarted it and got nothing.
iptables is turned off and still nothing.
Jason L. Amerson
From: Moreno Andreo
Sent: Thursday, November 21, 2019 11:27 AM
To: pgsql-general@lists.postgresql.org
Subject: ***SPAM*** Re: [SPAM] Remote Connection
On Thu, 21 Nov 2019 at 15:32, Peter J. Holzer wrote:
> On 2019-11-21 09:43:26 +, Geoff Winkless wrote:
> > It wasn't meant to be insulting, I meant "esoteric" in the strict
> > sense: that you need to have specific knowledge to parse them.
>
> I didn't understand it as insulting (why would
1) I am not sure if Postgres server is listening on port 5432. How do I
check?
2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U postgres."
I even tried to telnet to it using the static IP and port 5432 but it would
not connect. I can connect to it remotely using the static IP
Thank you everyone for your responses. It is great to see so much feedback.
Based upon all the responses, I was able to successfully set my tablespace
doing the following:
- Using /usr/data/pgdata96_sebastien as target tablespace directory
- Permissions were set to: chmod postgres:postgres
Hi Laurenz,
I was writing select from ""table"" as a template. We have to do this for a
bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first
timestamp, extract all meaningfull tables, and then pitr to the second
timestamp
so as to be able to script a kind of
Il 21/11/19 15:21, Jason L. Amerson ha
scritto:
could not
connect to server: Connection refused (Ox274D/10061) Is
the server running on host " xx.xx.xx.xx" and accepting
TCP/IP
Hi everybody.
I stumbled upon a weird problem with the query planner. I have a query
on a typical EAV schema:
SELECT contacts.id
FROM contacts
LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS
company_id, contacts_values.id AS id
FROM contacts_values
On 11/21/19 8:12 AM, stan wrote:
Please reply to list also
Ccing list.
On Thu, Nov 21, 2019 at 07:56:10AM -0800, Adrian Klaver wrote:
On 11/21/19 6:35 AM, stan wrote:
If the 1st rule matches, I am thinking this will override any rule I put in
after such as:
local all postgres md5
Is my
On Thu, 2019-11-21 at 17:07 +0100, Marc Millas wrote:
> you say "extract the data you need"
> That is exactly the point of my question, as the PITR step was obvious.
> How to guess "what is the data" I need ??
Well, you asked for the contents of a table AS OF TIMESTAMP .
That means you know
Hi,
funny enough, this pb looks similar to mine.
the point is: how to guess: what is the "data I need" ??
Looks like we are looking for a way to ask postgres: which transactions
have occurred between this and that.
Obviously, if we can have, online, both the db after the PITR and the db
On Thu, 2019-11-21 at 21:23 +0530, P V Tekawade wrote:
> For my work with Postgres 11.5, I needed functionality that unlogged tables
> are automatically dropped at the commit time, but I found that ON COMMIT
> option is only supported with temporary table.
> I would like to understand reasons why
On 11/21/19 7:15 AM, Jason L. Amerson wrote:
Steve,
I cannot connect to the server by “psql -h xx.xx.xx.xx.” I can connect
to my Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client
Sébastien:
On Thu, Nov 21, 2019 at 3:42 PM Sébastien Bihorel wrote:
.
> /home/sebastien/data $ ls -l
> drwxr-xr-x 2 sebastien postgres 4096 Nov 19 09:14 pgdata96_sebastien
> Starting psql as sebastien, I tried the following requests and got the
> following errors:
...
> The manual states
Hi Laurenz,
you say "extract the data you need"
That is exactly the point of my question, as the PITR step was obvious.
How to guess "what is the data" I need ??
The timestamp stuff within Oracle was providing exactly that: get all mods
from a given table that did occur within a given timeframe.
On 11/21/19 6:35 AM, stan wrote:
Thanks, found it, I believe:
local all postgres
Good to know first match wins, that is different than some other systems I
am used to.
OK, there appears to be a bit more than I thought to this. Here is the line I
believe is causing me
Hi
For my work with Postgres 11.5, I needed functionality that unlogged tables
are automatically dropped at the commit time, but I found that ON COMMIT
option is only supported with temporary table.
I would like to understand reasons why this option is limited to temporary
tables? Is there any
On 11/21/19 6:48 AM, Sébastien Bihorel wrote:
Hi,
I am new to this list and reaching out because I am having troubles
setting up a local PostgreSQL database on my laptop. Maybe, as a
preamble, I should say that I am pretty much a novice trying to teach
myself and get practical experience
I cannot connect to the server by psql -h xx.xx.xx.xx. I can connect to my
Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client machine or if I run psql -h xx.xx.xx.xx while
using my
Sébastien Bihorel wrote:
Hi,
I am new to this list and reaching out because I am having troubles
setting up a local PostgreSQL database on my laptop. Maybe, as a
preamble, I should say that I am pretty much a novice trying to teach
myself and get practical experience about database and SQL
On 2019-11-21 09:43:26 +, Geoff Winkless wrote:
> On Wed, 20 Nov 2019 at 22:48, Peter J. Holzer wrote:
> >
> > On 2019-11-19 11:37:04 +, Geoff Winkless wrote:
> > > Even if you do that you're still requiring the user to parse syntax
> > > according to esoteric rules.
> >
> > Oh, please.
On Thu, 2019-11-21 at 09:11 -0600, Ron wrote:
> After adding another 350GB to the data/base filesystem, the storage team
> said that the virtual LUN on our ESX host is full. No more expansion of
> this database until and unless they create a new LUN (on storage replicated
> to a different DC
On 11/20/19 11:12 PM, Matthias Apitz wrote:
Hello,
We found and use for ESQL/C the debug feature:
ECPGdebug(int on, FILE *stream);
with very good results. Is there something similar for the psql
interpreter to debug the query of complex SQL statements like this
monster here:
SELECT
On 11/21/19 6:51 AM, Laurenz Albe wrote:
On Thu, 2019-11-21 at 06:55 -0500, stan wrote:
You can use tablespaces in PostgreSQL, which are directories on a
different file system, to put your data elsewhere.
But that has very limited use-cases, and normally you don't
create a tablespace.
About
On 11/21/19 6:51 AM, Laurenz Albe wrote:
On Thu, 2019-11-21 at 06:55 -0500, stan wrote:
You can use tablespaces in PostgreSQL, which are directories on a
different file system, to put your data elsewhere.
But that has very limited use-cases, and normally you don't
create a tablespace.
About
On 11/20/19 5:54 PM, Dave Hughes wrote:
> Thanks for the tips Joe! After fighting with this all day, I realized
> while I was testing this, I was logging into the database as the
> "postgres" user. For some reason those actions were not being logged.
> But once I logged in as another superuser
Steve,
I cannot connect to the server by "psql -h xx.xx.xx.xx." I can connect to my
Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client machine or if I run "psql -h xx.xx.xx.xx"
On 11/21/19 8:59 AM, Laurenz Albe wrote:
On Thu, 2019-11-21 at 08:54 -0600, Ron wrote:
Don't create tablespaces.
Stick with the default tablespace.
Why?
Because you won't need them.
Tablespaces have a limited number of use cases:
- Distribute I/O across several devices (you can do the same
On Thu, 2019-11-21 at 08:54 -0600, Ron wrote:
> > Don't create tablespaces.
> > Stick with the default tablespace.
>
> Why?
Because you won't need them.
Tablespaces have a limited number of use cases:
- Distribute I/O across several devices (you can do the same
on a lower level using
On 11/21/19 8:52 AM, Laurenz Albe wrote:
On Thu, 2019-11-21 at 09:48 -0500, Sébastien Bihorel wrote:
Now, I would like to assign a particular disk location for the tablespace used
by this database but I am getting all kinds of errors apparently linked to
folder permissions.
Don't create
On Thu, 2019-11-21 at 09:48 -0500, Sébastien Bihorel wrote:
> Now, I would like to assign a particular disk location for the tablespace used
> by this database but I am getting all kinds of errors apparently linked to
> folder permissions.
Don't create tablespaces.
Stick with the default
On Thu, 2019-11-21 at 14:50 +0100, Marc Millas wrote:
> due to a set of bugs and wrong manip, an inappropriate update have been done
> into a production DB.
> After that, quite a long set of valuables inserts and updates have been done
> and needs to be kept.
> Obviously getting a backup and
On 11/21/19 8:42 AM, stan wrote:
On Thu, Nov 21, 2019 at 08:18:21AM -0600, Ron wrote:
[snip]
Yes, but ... schema in Postgres are /different/ from Oracle schema.?? In
Postgres, the CREATE SCHEMA command creates a schema, whereas CREATE USER
creates a schema in Oracle.
I am still struggling
On Thu, 2019-11-21 at 06:55 -0500, stan wrote:
> It seems to me that I can have one Postgres "server" running listening on a
> single port on a single machine. It appears that the data files for this
> "server" are managed internally by the Postgres server instance, and I
> have no control of what
Hi,
I am new to this list and reaching out because I am having troubles setting
up a local PostgreSQL database on my laptop. Maybe, as a preamble, I should
say that I am pretty much a novice trying to teach myself and get practical
experience about database and SQL queries (I have some general
On 21/11/2019 14:30, Ekaterina Amez wrote:
El 21/11/19 a las 15:21, Jason L. Amerson escribió:
I am at a loss for what to do. I have read article after article
about how to allow remote connections on my PostgreSQL server and
none of what the articles say do, worked for me. I have edited
On Thu, Nov 21, 2019 at 08:18:21AM -0600, Ron wrote:
> On 11/21/19 5:55 AM, stan wrote:
> > On Wed, Nov 20, 2019 at 04:24:40PM -0600, Ron wrote:
> > > On 11/20/19 4:03 PM, stan wrote:
> > > > I am working on a fairly small application to use for managing a
> > > > companies
> > > > business.
>
On Thu, 2019-11-21 at 12:11 +0400, Jill Jade wrote:
> I am new to Postgres and I have a query.
>
> I have updated a table which I should not have.
>
> Is there a way to extract the transactions from the WAL and get back the
> previous data?
>
> Is there a tool that can help to get back the
Hi,
I am new to this list and reaching out because I am having troubles setting
up a local PostgreSQL database on my laptop. Maybe, as a preamble, I should
say that I am pretty much a novice trying to teach myself and get practical
experience about database and SQL queries (I have some general
On Thu, Nov 21, 2019 at 09:15:02AM -0500, stan wrote:
> On Thu, Nov 21, 2019 at 02:05:09PM +0100, Magnus Hagander wrote:
> > On Thu, Nov 21, 2019 at 1:46 PM stan wrote:
> >
> > > I am trying to set up to do some work with pg_dump, and I would like to be
> > > able to connect from my normal
El 21/11/19 a las 15:21, Jason L. Amerson escribió:
I am at a loss for what to do. I have read article after article about
how to allow remote connections on my PostgreSQL server and none of
what the articles say do, worked for me. I have edited the
“postgresql.conf” file and changed
On Thu, Nov 21, 2019 at 04:56 Jill Jade wrote:
> Hello everyone,
>
> I am new to Postgres and I have a query.
>
> I have updated a table which I should not have.
>
> Is there a way to extract the transactions from the WAL and get back the
> previous data?
>
> Is there a tool that can help to
I am at a loss for what to do. I have read article after article about how
to allow remote connections on my PostgreSQL server and none of what the
articles say do, worked for me. I have edited the "postgresql.conf" file and
changed "listen_address = 'localhost' to listen_address = '*'. I have
On 11/21/19 5:55 AM, stan wrote:
On Wed, Nov 20, 2019 at 04:24:40PM -0600, Ron wrote:
On 11/20/19 4:03 PM, stan wrote:
I am working on a fairly small application to use for managing a companies
business.
I have a "production" instance hosted by one of the cloud providers, and 2
other
On Thu, Nov 21, 2019 at 02:05:09PM +0100, Magnus Hagander wrote:
> On Thu, Nov 21, 2019 at 1:46 PM stan wrote:
>
> > I am trying to set up to do some work with pg_dump, and I would like to be
> > able to connect from my normal user to do this. This is on a Ubunt 18.04
> > installation. I have
Hi,
due to a set of bugs and wrong manip, an inappropriate update have been
done into a production DB.
After that, quite a long set of valuables inserts and updates have been
done and needs to be kept.
Obviously getting a backup and applying pitr will get us just before the
offending update.
Now,
On Thu, Nov 21, 2019 at 1:46 PM stan wrote:
> I am trying to set up to do some work with pg_dump, and I would like to be
> able to connect from my normal user to do this. This is on a Ubunt 18.04
> installation. I have added the follwing to pg_hba.conf:
>
> hostall all
I am trying to set up to do some work with pg_dump, and I would like to be
able to connect from my normal user to do this. This is on a Ubunt 18.04
installation. I have added the follwing to pg_hba.conf:
hostall all 0.0.0.0/0 md5
hostssl all
> uuid character varying(45) NOT NULL,
Just a comment.
IF this is a real UUID ( RFC 4122, ISO/IEC 9834-8:2005 ) ;
THEN you can use the built in "UUID Type"
https://www.postgresql.org/docs/11/datatype-uuid.html
*"UUID would be the fastest because its 128 bits -> 16 bytes and
comparisons are
Hello everyone,
I am new to Postgres and I have a query.
I have updated a table which I should not have.
Is there a way to extract the transactions from the WAL and get back the
previous data?
Is there a tool that can help to get back the transactions?
Thanks in advance.
Regards,
Jill
On Wed, Nov 20, 2019 at 04:24:40PM -0600, Ron wrote:
> On 11/20/19 4:03 PM, stan wrote:
> > I am working on a fairly small application to use for managing a companies
> > business.
> >
> > I have a "production" instance hosted by one of the cloud providers, and 2
> > other instances. This is
Tony Shelver schrieb am 21.11.2019 um 07:33:
> Well then SQL Server breaks that rule big time :)
I am aware of that - but at the end it's essentially the only DBMS (except for
Sybase because of their common roots) that works that way.
A migration from SQL Server to Oracle (or MySQL or DB2 or
On Wed, 20 Nov 2019 at 22:48, Peter J. Holzer wrote:
>
> On 2019-11-19 11:37:04 +, Geoff Winkless wrote:
> > Even if you do that you're still requiring the user to parse syntax
> > according to esoteric rules.
>
> Oh, please. Those "esoteric rules" have been in wide-spread use for
> decades.
86 matches
Mail list logo
