Re: [GENERAL] pgDay Asia / talks / lightning talks
It's pity I miss this conference, since I learned about it too late. On Wed, Feb 17, 2016 at 8:46 AM, Satoshi Nagayasu wrote: > Hi Dan, > > 2016-02-16 20:43 GMT+09:00 Daniel Pocock : > > Is this the place to ask questions about pgDay Asia[1] or is there > > another mailing list for it? The mailing list link on the pgDay Asia > > web site just takes me to a marketing list[2]. The seasiapug list[3] > > looks very quiet. > > > 2. > http://uptime.us2.list-manage.com/subscribe/post?u=8b6e2840d44be26e9f646b9f9&id=128a96a18a > > This is the list I have set up, and it is intended to provide some > update information > about the conference and the web site, especially for the attendees. > > And if you have any question or comments, please send to pgday-asia > [at] googlegroups.co.jp > > Regards, > -- > Satoshi Nagayasu > > > -- > 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 on memory management sysv/posix on Linux
Hi I have a question about memory management in PostgreSQL. I understand the default on Linux usually is "posix" which creates files in /dev/shm. With the default settings of 128MB for shared_buffers this is the result: ls -la /dev/shm total 4 drwxrwxrwt 2 root root 60 Feb 17 11:19 . drwxr-xr-x 18 root root 3100 Feb 17 09:36 .. -rw--- 1 postgres postgres 2316 Feb 17 11:19 PostgreSQL.1804289383 Question: Why is this file only 2316 bytes? Switching to sysv (same setting for shared_buffers): ipcs -m -- Shared Memory Segments key shmid owner perms bytes nattch status 0x0052e2c1 622592 postgres 600 56 5 0x6b8b4567 655361 postgres 600 2316 5 Again 2316 bytes. Why? Is the 56 bytes segment related to Robert's post and only used for determining which processes are attached? http://rhaas.blogspot.com/2012/06/absurd-shared-memory-limits.html Thanks in advance Daniel
Re: [GENERAL] Custom conflict handlers
Can someone help with bdr conflict handlers? What is ch_proc ..? On 13-Feb-2016 8:11 pm, "Nikhil" wrote: > This is for system that use BDR (Bi-Directional Replication). BDR > documentation > http://bdr-project.org/docs/next/functions-conflict-handlers.html talks > about postgresql functions. I am looking for samples. > > Best Regards, > Nikhil > > On Sat, Feb 13, 2016 at 5:31 PM, Nikhil wrote: > >> Hello, >> >> Is there any documentation with details of how to custom conflict >> handlers? Wanted to write update-update conflict handler. Any help is >> highly appreciated. >> >> Best Regards, >> Nikhil >> > >
Re: [GENERAL] Question on memory management sysv/posix on Linux
Daniel Westermann writes: > ls -la /dev/shm > total 4 > drwxrwxrwt 2 root root 60 Feb 17 11:19 . > drwxr-xr-x 18 root root 3100 Feb 17 09:36 .. > -rw--- 1 postgres postgres 2316 Feb 17 11:19 PostgreSQL.1804289383 > Question: Why is this file only 2316 bytes? It holds a DSM control header, nothing more. If you were actually doing anything with dynamic shared memory, you'd see more such files. 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
[GENERAL] Multiple databases and shared_buffers
We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers. thanks.
[GENERAL] Charlotte Postgres User Group
If you live near or around Charlotte, please join us for the inaugural meeting of the Charlotte PUG on March 1, followed by a second meeting on April 11 featuring Bruce Momjian. More information about the two events can be found here: http://www.meetup.com/Charlotte-PostgreSQL-User-Group Also if you are travelling through Charlotte, NC or you just like to visit the "Queen City" you are welcome to stop by an give a talk to the Charlotte PUG. Just contact me when you would be in town and I can pencil you in for a talk or a presentation. Thanks, Boyan Botev
[GENERAL] Replaying xlogs from beginning
I'm looking for answers to this question, but so far haven't turned up a
usable answer. Perhaps I'm asking it the wrong way.
I want to replay the xlogs from the beginning of time up until a particular
time. The problem is, the time is before the first base backup. But I have
all the xlogs since the database was initialized.
To test this, I initialized a new DB and put a few xlogs in its pg_xlog
directory, and I create a simple recovery.conf ("restore_command=false").
When I start it up, I get something like "DETAIL: WAL file database system
identifier is 6221786353392811102, pg_control database system identifier is
6252279422905597461."
The most important question is: How do I recover to PIT, starting from
initialization, without a base backup, provided I have all xlog files.
The secondary question is: How can I test this with a new instance?
--
Otheus
otheus.u...@gmail.com
otheus.shell...@uibk.ac.at
[GENERAL] Appending key-value to JSONB tree
I have a "user" document with a key "tokens" and I would like to write a
stored procedure for adding new token key-value pairs to the "tokens" part
of the tree without removing the old values. I have figured out how to
replace the existing value in the "tokens", but I cannot seem to wrap my
head around appending a new key-value pair. Could someone suggest an
approach (using PostgreSQL 9.5 BTW)...
Here's my existing stored proc:
CREATE OR REPLACE FUNCTION set_user_token(email TEXT, expiration INTERVAL)
RETURNS TEXT AS $$
WITH newtoken AS (
SELECT
jsonb_build_object(random_string(32), (now()+$2)) token
),
updated AS (
SELECT
jsonb_set(data::jsonb, '{"tokens"}', (SELECT token FROM newtoken)) newdata
FROM
users
WHERE
email=$1
),
updatecomplete AS (
UPDATE
cc_users
SET
data=(SELECT newdata FROM updated)
WHERE
email=$1
)
SELECT jsonb_pretty(token) FROM newtoken $$
LANGUAGE SQL;
Thanks in advance!!!
Deven Phillips
[GENERAL] Log Monitoring with PG Admin
Hi, i am running and RDS instance on AWS but monitoring logs is a bit cumbersome. Is there a way to do the through pgadmin3 ? Like tailing the logfile? Does anyone know when 9.5 will be available on aws? Thanks Alex
Re: [GENERAL] Replaying xlogs from beginning
I came up with an answer to the _second_ question (how do I do this from a
new instance?).
In the new instance directory:
1. Hack the system ID in the global/pg_control file to that of the original
instance.
1a. Use pg_controlinfo to get the hex version of the control id:
$ pg_controldata | perl -l -n -e 'if (/^Database system
identifier:\s+(\d+)$/) { $sysid=sprintf("%x",$1);print join(" ",reverse
$sysid=~m/../g); exit; }'
fb fb 59 56 b9 31 58 53
1b. Use a hex editor (vim with xxd / xxd -r will do) to replace the
first bytes with these values.
2. The new control file will be unusable because its CRC won't match. Fix
that with pg_resetlog.
$ pg_resetlog -f
3. Copy the very first pg_xlog file into place. The rest can be copied by
hand or found using whatever you use for the "restore_command"
4. Create recovery.conf file.
Start instance.
Again, this worked for me. What I want to be sure of is: does this really
work? And I still want to know: can I do this without creating a new
instance?
Re: [GENERAL] Replaying xlogs from beginning
On Wed, Feb 17, 2016 at 9:16 AM, otheus uibk wrote: > I came up with an answer to the _second_ question (how do I do this from a > new instance?). > [...] > Again, this worked for me. What I want to be sure of is: does this really > work? > I cannot definitively answer the question but it you can get the system to boot and the data looks good what I would do is pg_dump the result and then pg_restore is back into a clean cluster. And I still want to know: can I do this without creating a new instance? > Someone considerably more informed than I would need to answer this. David J.
Re: [GENERAL] Log Monitoring with PG Admin
On Wed, Feb 17, 2016 at 9:05 AM, Alex Magnum wrote: > Hi, > i am running and RDS instance on AWS but monitoring logs is a bit > cumbersome. > Is there a way to do the through pgadmin3 ? Like tailing the logfile? > Only if you are capable of doing something like this on RDS: http://www.postgresql.org/docs/9.4/static/file-fdw.html (in theory a plain SQL COPY command could work here but I'm not sure how that plays in reality) Otherwise pgAdmin3 is like any other piece of client software that talks to the server using SQL. It doesn't get any special access to the server's environment including its filesystem and thus logs. The above would pre-suppose that RDS PostgreSQL even keeps its logs in a flat file directly accessible by PostgreSQL. David J.
Re: [GENERAL] Replaying xlogs from beginning
otheus uibk writes:
> I came up with an answer to the _second_ question (how do I do this from a
> new instance?).
> In the new instance directory:
> 1. Hack the system ID in the global/pg_control file to that of the original
> instance.
>1a. Use pg_controlinfo to get the hex version of the control id:
> $ pg_controldata | perl -l -n -e 'if (/^Database system
> identifier:\s+(\d+)$/) { $sysid=sprintf("%x",$1);print join(" ",reverse
> $sysid=~m/../g); exit; }'
> fb fb 59 56 b9 31 58 53
>1b. Use a hex editor (vim with xxd / xxd -r will do) to replace the
> first bytes with these values.
> 2. The new control file will be unusable because its CRC won't match. Fix
> that with pg_resetlog.
> $ pg_resetlog -f
> 3. Copy the very first pg_xlog file into place. The rest can be copied by
> hand or found using whatever you use for the "restore_command"
> 4. Create recovery.conf file.
> Start instance.
> Again, this worked for me. What I want to be sure of is: does this really
> work? And I still want to know: can I do this without creating a new
> instance?
No, and no. You're assuming that the only significant aspect of initdb's
output that can vary from run to run is the database system ID. This is
false. Quite aside from the question of whether you gave initdb the exact
same arguments each time, the resulting DB also has dependencies on the
exact set of locales installed on the system (from which the pg_collation
catalog is filled). If you're not running the exact same minor version
of PG that you were using before, there might be intentional small
differences in the initial catalog contents. There's some intentional
randomization in btree index insertion, which means that the contents
of system catalog indexes might not be bitwise the same from one initdb
run to the next, even if all else is the same. There may be still other
causes of variance that I'm not thinking of at the moment. Any one of
these differences could be harmless, but it could also mean that replaying
a WAL sequence against the database will result in inconsistencies.
If you're lucky this technique will work, but it's not reliable and not
supported. You really need to take an initial base backup after running
initdb.
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
[GENERAL] Pgsql troubleshooting & Iscsi
Hi everybody, I installed a postgresql database on Redhat 7.1 and I decided to move the database on an ISCSI device (LUN) inside a logical volume, mounted at starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql At the boot of the server, postgresql.service is in failed status. In messages.log : systemd: mounting /var/lib/pgsql starting PostgreSQL database server kernel sdv: unknown partition table sd 2:0:0:0: [sdb] attached SCSI disk xfs (dm-4): Mounting V4 Filesystem postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. When I'm logged on the server, if it try to start manually the database : systemctl start postgresql --> OK (and I don't lose any data, database is available) I think it's a problem of order in the boot process : network service must be started, then iscsi, then lvm etc... So I tried to force dependencies on the /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service iscsi.service" etc... but the result is the same : failure in starting postgresql systemd: Starting Remote File Systems (Pre) systemd: Reached Remote File Systems (Pre) systemd: mounting /var/lib/pgsql systemd: starting PostgreSQL database server kernel sdb: unknown partition table postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty kernel: xfs (dm-4): Mounting V4 Filesystem postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. Unit postgresql.service entered failed state sd 2:0:0:0: [sdb] attached SCSI disk starting LVM2 PV scan on device 8:16 kernel: xfs (dm-3): Ending clean mount systemd: Mounted /var/lib/pgsql Starting Remote File Systems Reached target Remote File Systems Any ideas ? regards, Magique -- 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] Pgsql troubleshooting & Iscsi
On 02/17/2016 09:07 AM, p...@free.fr wrote: Hi everybody, I installed a postgresql database on Redhat 7.1 and I decided to move the database on an ISCSI device (LUN) inside a logical volume, mounted at starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql At the boot of the server, postgresql.service is in failed status. In messages.log : systemd: mounting /var/lib/pgsql starting PostgreSQL database server kernel sdv: unknown partition table sd 2:0:0:0: [sdb] attached SCSI disk xfs (dm-4): Mounting V4 Filesystem postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. When I'm logged on the server, if it try to start manually the database : systemctl start postgresql --> OK (and I don't lose any data, database is available) I think it's a problem of order in the boot process : network service must be started, then iscsi, then lvm etc... So I tried to force dependencies on the /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service iscsi.service" etc... but the result is the same : failure in starting postgresql systemd: Starting Remote File Systems (Pre) systemd: Reached Remote File Systems (Pre) systemd: mounting /var/lib/pgsql ^ File system not mounted yet. systemd: starting PostgreSQL database server kernel sdb: unknown partition table postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty ^ Cannot find mount point kernel: xfs (dm-4): Mounting V4 Filesystem postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. Unit postgresql.service entered failed state sd 2:0:0:0: [sdb] attached SCSI disk starting LVM2 PV scan on device 8:16 kernel: xfs (dm-3): Ending clean mount systemd: Mounted /var/lib/pgsql ^ Now file system is mounted. Starting Remote File Systems Reached target Remote File Systems Any ideas ? Find where file system mounting is completed and start Postgres after that. I am still figuring out systemd, so I can not offer anything more concrete. regards, Magique -- 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] Pgsql troubleshooting & Iscsi
On 02/17/2016 06:07 PM, p...@free.fr wrote: Hi everybody, I installed a postgresql database on Redhat 7.1 and I decided to move the database on an ISCSI device (LUN) inside a logical volume, mounted at starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql At the boot of the server, postgresql.service is in failed status. In messages.log : systemd: mounting /var/lib/pgsql starting PostgreSQL database server kernel sdv: unknown partition table sd 2:0:0:0: [sdb] attached SCSI disk xfs (dm-4): Mounting V4 Filesystem postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. When I'm logged on the server, if it try to start manually the database : systemctl start postgresql --> OK (and I don't lose any data, database is available) I think it's a problem of order in the boot process : network service must be started, then iscsi, then lvm etc... So I tried to force dependencies on the /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service iscsi.service" etc... but the result is the same : failure in starting postgresql systemd: Starting Remote File Systems (Pre) systemd: Reached Remote File Systems (Pre) systemd: mounting /var/lib/pgsql systemd: starting PostgreSQL database server kernel sdb: unknown partition table postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty kernel: xfs (dm-4): Mounting V4 Filesystem postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. Unit postgresql.service entered failed state sd 2:0:0:0: [sdb] attached SCSI disk starting LVM2 PV scan on device 8:16 kernel: xfs (dm-3): Ending clean mount systemd: Mounted /var/lib/pgsql Starting Remote File Systems Reached target Remote File Systems Any ideas ? regards, Magique Put _netdev in the option field in fstab. This indicates that the device must be mounted after network is available. I have for example : /dev/disk/by-path/ip-10.48.4.1:3260-iscsi-iqn.2004-04.com.qnap:ts-851:iscsi.lun0.e6ae62-lun-0-part1 /data3 btrfs _netdev 0 0 Regards Jeroen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re : Re: [GENERAL] Pgsql troubleshooting & Iscsi
I already try to change the dependencies with postgresql.service (adding as a prerequesite lvm scan, iscsi) . The mounting began before postgresql but the mount was successfull after the failure. - Mail d'origine - De: Adrian Klaver À: p...@free.fr, pgsql-general@postgresql.org Envoyé: Wed, 17 Feb 2016 18:35:15 +0100 (CET) Objet: Re: [GENERAL] Pgsql troubleshooting & Iscsi On 02/17/2016 09:07 AM, p...@free.fr wrote: > Hi everybody, > > I installed a postgresql database on Redhat 7.1 and I decided to move the > database on an ISCSI device (LUN) inside a logical volume, mounted at > starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql > > At the boot of the server, postgresql.service is in failed status. > > In messages.log : > systemd: mounting /var/lib/pgsql > starting PostgreSQL database server > kernel sdv: unknown partition table > sd 2:0:0:0: [sdb] attached SCSI disk > xfs (dm-4): Mounting V4 Filesystem > postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty > postgresql.service: control process exited, code=exited status=1 > Failed to start PostgreSQL database server. > > > When I'm logged on the server, if it try to start manually the database : > systemctl start postgresql --> OK (and I don't lose any data, database is > available) > > I think it's a problem of order in the boot process : network service must be > started, then iscsi, then lvm etc... So I tried to force dependencies on the > /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service > iscsi.service" etc... but the result is the same : failure in starting > postgresql > > > > systemd: Starting Remote File Systems (Pre) > systemd: Reached Remote File Systems (Pre) > systemd: mounting /var/lib/pgsql ^ File system not mounted yet. > systemd: starting PostgreSQL database server > kernel sdb: unknown partition table > postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty ^ Cannot find mount point > kernel: xfs (dm-4): Mounting V4 Filesystem > postgresql.service: control process exited, code=exited status=1 > Failed to start PostgreSQL database server. > Unit postgresql.service entered failed state > sd 2:0:0:0: [sdb] attached SCSI disk > starting LVM2 PV scan on device 8:16 > kernel: xfs (dm-3): Ending clean mount > systemd: Mounted /var/lib/pgsql ^ Now file system is mounted. > Starting Remote File Systems > Reached target Remote File Systems > > Any ideas ? Find where file system mounting is completed and start Postgres after that. I am still figuring out systemd, so I can not offer anything more concrete. > > regards, > Magique > > -- 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: Re : Re: [GENERAL] Pgsql troubleshooting & Iscsi
On 02/17/2016 10:30 AM, p...@free.fr wrote: I already try to change the dependencies with postgresql.service (adding as a prerequesite lvm scan, iscsi) . The mounting began before postgresql but the mount was successfull after the failure. Exactly, the drive was not mounted when the Postgres service started, so Postgres could not find its data directory. From your log: postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty - Mail d'origine - De: Adrian Klaver À: p...@free.fr, pgsql-general@postgresql.org Envoyé: Wed, 17 Feb 2016 18:35:15 +0100 (CET) Objet: Re: [GENERAL] Pgsql troubleshooting & Iscsi On 02/17/2016 09:07 AM, p...@free.fr wrote: Hi everybody, I installed a postgresql database on Redhat 7.1 and I decided to move the database on an ISCSI device (LUN) inside a logical volume, mounted at starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql At the boot of the server, postgresql.service is in failed status. In messages.log : systemd: mounting /var/lib/pgsql starting PostgreSQL database server kernel sdv: unknown partition table sd 2:0:0:0: [sdb] attached SCSI disk xfs (dm-4): Mounting V4 Filesystem postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. When I'm logged on the server, if it try to start manually the database : systemctl start postgresql --> OK (and I don't lose any data, database is available) I think it's a problem of order in the boot process : network service must be started, then iscsi, then lvm etc... So I tried to force dependencies on the /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service iscsi.service" etc... but the result is the same : failure in starting postgresql systemd: Starting Remote File Systems (Pre) systemd: Reached Remote File Systems (Pre) systemd: mounting /var/lib/pgsql ^ File system not mounted yet. systemd: starting PostgreSQL database server kernel sdb: unknown partition table postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty ^ Cannot find mount point kernel: xfs (dm-4): Mounting V4 Filesystem postgresql.service: control process exited, code=exited status=1 Failed to start PostgreSQL database server. Unit postgresql.service entered failed state sd 2:0:0:0: [sdb] attached SCSI disk starting LVM2 PV scan on device 8:16 kernel: xfs (dm-3): Ending clean mount systemd: Mounted /var/lib/pgsql ^ Now file system is mounted. Starting Remote File Systems Reached target Remote File Systems Any ideas ? Find where file system mounting is completed and start Postgres after that. I am still figuring out systemd, so I can not offer anything more concrete. regards, Magique -- 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] Multiple databases and shared_buffers
On Thu, Feb 18, 2016 at 1:54 AM, Data Cruncher wrote: > We will be creating multiple databases in a cluster (instance). Is there > any way to separate shared_buffers for each database? Looks like not since > PG does not allow user created shared buffers. shared_buffers parameter is for the whole PostgreSQL cluster shared by all the databases in the cluster. Regards, Venkata B N Fujitsu Australia
Re: [GENERAL] Multiple databases and shared_buffers
On 2/17/2016 6:54 AM, Data Cruncher wrote: We will be creating multiple databases in a cluster (instance). Is there any way to separate shared_buffers for each database? Looks like not since PG does not allow user created shared buffers. you would need to run multiple instances if you feel you need that level of control over shared_buffers. -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Multiple databases and shared_buffers
I think this begs the question "Why do you think you need to separate the shared_buffers"? What version of PostgreSQL are you using? What is your O/S? How many CPU's on your server? How much memory? On Wed, Feb 17, 2016 at 5:21 PM, John R Pierce wrote: > On 2/17/2016 6:54 AM, Data Cruncher wrote: > > We will be creating multiple databases in a cluster (instance). Is there > any way to separate shared_buffers for each database? Looks like not since > PG does not allow user created shared buffers. > > > > you would need to run multiple instances if you feel you need that level > of control over shared_buffers. > > > -- > john r pierce, recycling bits in santa cruz > > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
[GENERAL] BRIN Usage
Hi: I feel it is a stupid question. Can BRIN index enforce uniqueness? My issue is the column I'd like to apply BRIN index also needs to be unique (think of timestamp as primary key). Thanks
[GENERAL] Pgsql troubleshooting & Iscsi
I already mount with the netdev option. The network service is started before postgresql but not lvm for. I tried to start lvm service as dependency but no changes. - Mail d'origine - De: Jeroen van Iddekinge À: p...@free.fr, pgsql-general@postgresql.org Envoyé: Wed, 17 Feb 2016 18:51:42 +0100 (CET) Objet: Re: [GENERAL] Pgsql troubleshooting & Iscsi On 02/17/2016 06:07 PM, p...@free.fr wrote: > Hi everybody, > > I installed a postgresql database on Redhat 7.1 and I decided to move the > database on an ISCSI device (LUN) inside a logical volume, mounted at > starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql > > At the boot of the server, postgresql.service is in failed status. > > In messages.log : > systemd: mounting /var/lib/pgsql > starting PostgreSQL database server > kernel sdv: unknown partition table > sd 2:0:0:0: [sdb] attached SCSI disk > xfs (dm-4): Mounting V4 Filesystem > postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty > postgresql.service: control process exited, code=exited status=1 > Failed to start PostgreSQL database server. > > > When I'm logged on the server, if it try to start manually the database : > systemctl start postgresql --> OK (and I don't lose any data, database is > available) > > I think it's a problem of order in the boot process : network service must be > started, then iscsi, then lvm etc... So I tried to force dependencies on the > /usr/lib/systemd/system/postgresql.service adding "After=lvm-pgscan.service > iscsi.service" etc... but the result is the same : failure in starting > postgresql > > > > systemd: Starting Remote File Systems (Pre) > systemd: Reached Remote File Systems (Pre) > systemd: mounting /var/lib/pgsql > systemd: starting PostgreSQL database server > kernel sdb: unknown partition table > postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty > kernel: xfs (dm-4): Mounting V4 Filesystem > postgresql.service: control process exited, code=exited status=1 > Failed to start PostgreSQL database server. > Unit postgresql.service entered failed state > sd 2:0:0:0: [sdb] attached SCSI disk > starting LVM2 PV scan on device 8:16 > kernel: xfs (dm-3): Ending clean mount > systemd: Mounted /var/lib/pgsql > Starting Remote File Systems > Reached target Remote File Systems > > Any ideas ? > > regards, > Magique > > Put _netdev in the option field in fstab. This indicates that the device must be mounted after network is available. I have for example : /dev/disk/by-path/ip-10.48.4.1:3260-iscsi-iqn.2004-04.com.qnap:ts-851:iscsi.lun0.e6ae62-lun-0-part1 /data3 btrfs _netdev 0 0 Regards Jeroen -- 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] BRIN Usage
On 18/02/2016 9:34 am, "Tom Smith" wrote: > > Hi: > > I feel it is a stupid question. > > Can BRIN index enforce uniqueness? > My issue is > the column I'd like to apply BRIN index also needs to be unique > (think of timestamp as primary key). Only btree supports unique. Is there a special reason not to use btree? I'm also finding it hard to imagine a case where a timestamp primary key is a good idea.
Re: [GENERAL] Pgsql troubleshooting & Iscsi
On Wed, Feb 17, 2016 at 06:07:29PM +0100, p...@free.fr wrote: > Hi everybody, > > I installed a postgresql database on Redhat 7.1 and I decided to move the > database on an ISCSI device (LUN) inside a logical volume, mounted at > starting of the machine (xfs formatted). The mounting point is /var/lib/pgsql > > At the boot of the server, postgresql.service is in failed status. > > In messages.log : > systemd: mounting /var/lib/pgsql > starting PostgreSQL database server > kernel sdv: unknown partition table > sd 2:0:0:0: [sdb] attached SCSI disk > xfs (dm-4): Mounting V4 Filesystem > postgresql-check-db-dir: "/var/lib/pgsql/data" is missing or empty > postgresql.service: control process exited, code=exited status=1 > Failed to start PostgreSQL database server. > > > When I'm logged on the server, if it try to start manually the database : > systemctl start postgresql --> OK (and I don't lose any data, database is > available) > > I think it's a problem of order in the boot process : network service must > be started, then iscsi, then lvm etc... So I tried to force dependencies > on the /usr/lib/systemd/system/postgresql.service adding > "After=lvm-pgscan.service iscsi.service" etc... but the result is > the same : failure in starting postgresql > > Any ideas ? Take a look at this thread: http://www.gossamer-threads.com/lists/linux/kernel/1332888 I'm not an XFS guru, but it seems that XFS does a bunch of checks just on/after mount, so perhaps PgSQL cannot access /var/lib/pgsql/data immediately. You could igive it a try forcing some delay in systemd conf file, or tweaking the PgSQL startup script. -- Alberto Cabello Sánchez Universidad de Extremadura -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JDBC behaviour
Hi We are facing issue with PostgreSQL JDBC behaviour in Java, under autocommit false mode, 1.in between if any transaction
