Re: Can't Remote connection by IpV6
On Thu, 2024-06-06 at 11:46 -0300, Marcelo Marloch wrote: > Hi everyone, is it possible to remote connect through IpV6? IpV4 > works fine but I cant connect through V6 > > postgresql.conf is to listen all address and pg_hba.conf is set > with host all all :: md5 i've tried ::/0 and ::0/0 but had no > success > > my provider is out of ipv4 and they're sending ips by cgnat if I > want a public ipv4 I have to sign a very expensive service fee > > thanks a lot listen '*' or listen '::' just for ipv6. Remember to adjust pg_hba.conf as well.
Re: Seeking help extricating data from Amazon RDS Aurora/Postgres
On Mon, 2024-01-29 at 14:22 -0500, Bill Mitchell wrote: > We are attempting to extract one of our database from Amazon RDS > Aurora/Postgres to another PostgreSQL cluster that is running > directly on EC2 instances. Aurora PostgreSQL supports logical replication and purports to use the native WAL, so I suppose it might be compatible with an open source PostgreSQL target. Probably worth testing.
Re: vacuumdb seems not to like option -j when run from crontab
On Mon, 2023-12-04 at 11:07 -0500, Ron Johnson wrote: > PG 9.6.24 (Yes, it's EOL.) > > When running "vacuumdb -p5433 -j4 --analyze tap_d" from a bash > prompt, it works as expected: > $ vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: vacuuming database "tap_d" > > But not when running from crontab: > vacuumdb -p5433 -j4 --analyze tap_d > vacuumdb: invalid option -- 'j' > Try "vacuumdb --help" for more information. > > Obviously I'm missing something, but don't see what it is. > Attached is the script it runs from. > Is your user and PATH the same? ie. are you running the same vacuumdb executable?
Re: Can user specification of a column value be required when querying a view ?
On Mon, 2023-11-20 at 13:44 -0800, Christophe Pettus wrote: > > > > On Nov 20, 2023, at 13:41, David Gauthier > > wrote: > > I want the users to be required to provide a value for ssn in the > > following query... > > "select * from huge_view where ssn = '106-91-9930' " > > I never want them to query the view without specifying ssn. > > It has to do with resources and practicality. > > > > Is there a way to do that ? > > Not in a way that PostgreSQL itself will enforce. If you are > concerned about a query running wild and taking up resources, > setting statement_timeout for the user that will be running these > queries is the best way forward. A user that has general access to > PostgreSQL and can run arbitrary queries will be able to craft a > query that takes up a lot of system time and memory without too > much trouble. If it's really about SSN's it might be more about bulk access to PII than performance. A function is probably the right choice in either case.
Re: connecting to new instance
On Fri, 2023-09-22 at 17:08 -0500, Brad White wrote: > I have the v15 service started and listening on 0.0.0.0:5434. > Through TCPView, I can see it listening on 5434, I can see the > previous version listening and connecting on 5432. > I can connect from localhost to port 5434. > I have ipv6 turned off in the network settings on both machines. > From any other client or server, I get > "Are you sure the server is running on 192.168.1.112 and accepting > connections on 5434? > The relevant line in pg_hba.conf looks like: > host all all 192.168.1.0/24 > password > > I can't see what else I'm missing. Probably the host firewall.
Re: Trying to understand a failed upgrade in AWS RDS
On Sun, 2023-05-21 at 07:56 -0700, Mike Lissner wrote: > > As far as I know it's impossible to reliably pg_upgrade a node > > that has subscriptions and eventually resume logical > > replication. > > > > > Should this go in the documentation somewhere? Maybe in the > pg_upgrade notes? I still don't understand the mechanism. You also > say that: > > > It's possible to make it work with some efforts in some basic > > configurations and / or if no changes happen on the publications > > > > > But that kind of surprises me too, actually, because it seemed like > pg_upgrade wiped out the LSN locations of the subcriber, making it > start all over. > > Upgrading a subscriber seems like something that could/should work, > so it should be documented if pg_upgrade is incompatible with > maintaining a subscription, shouldn't it? The docs are strangely silent on this. AFAIK pg_upgrade on either the publisher or subscriber breaks logical replication, which does make sense since pg_upgrade basically makes a new database cluster as it runs. There is a way to manually set the LSN position of an enabled=false replication slot, but I've failed to make that work right in tests so far.
Re: Issues Scaling Postgres Concurrency
On Mon, 2023-03-13 at 12:24 -0400, Harrison Borges wrote: > Hello everyone. > > I’m running into severe performance problems with Postgres as I > increase the number of concurrent requests against my backend. I’ve > identified that the bottleneck is Postgres, and to simplify the > test case, I created an endpoint that only does a count query on a > table with ~500k rows. At 5 concurrent users, the response time was > 33ms, at 10 users it was 60ms, and at 20 users it was 120ms. I'm no expert on high concurrency, but for something this simple I'd expect that you're just CPU bottlenecked. Count in PostgreSQL actually has to read all the rows in the table. And yeah you can't do too many of them at the same time.
Re: pg_upgrade 13.6 to 15.1?
On Sun, 2023-01-15 at 16:59 -0500, p...@pfortin.com wrote: > > > encodings for database "template1" do not match: old "UTF8", new > "SQL_ASCII" Failure, exiting You almost certainly don't want your new database to use SQL_ASCII. Init the new cluster with -E UTF8.
Re: pg_receivewal/xlog to ship wal to cloud
On Mon, 2022-07-18 at 15:55 -0400, neslişah demirci wrote: > Hi all, > > Wondering if anyone has any experience of using pg_receivewal/xlog > to ship wal files to GCP/S3? > I use archive_command to send WAL to S3. It works fine. I do gzip them before uploading, as they are usually pretty compressible. I use a lifecycle rule on the bucket to automatically delete ones older than I might need. It's not the fastest process in the world, and replay on our reporting server does occasionally fall behind, but I think that's true of WAL replay in general as a serialized process.
Re: Need to install Postgres Version 14 in Linux server with Client and migration steps.
On Mon, 2022-05-09 at 06:46 +, Rajamoorthy-CW, Thirumurugan 8361 wrote: > Hi Team, > > I need to install Postgres Version 14 in Linux server with Client > and migration steps. Can you please provide me the installation > steps document ? > PostgreSQL is kind of a do-it-yourself thing like all open source. No one is going to hand-hold you through all the steps of upgrading your particular environment. 1) contact your DBA or ops team. 2) Ask them to upgrade PostgreSQL If you are the DBA or ops team, the PostgreSQL documentation is at https://www.postgresql.org/docs/current Pay particular attention to the sections on Backup and Restore and https://www.postgresql.org/docs/current/pgupgrade.html. And I would recommend you build a test server and try it there a couple of times first. gl.
Re: Moving the master to a new server
On Tue, 2022-02-15 at 08:58 +1300, Glen Eustace wrote: > > But upgrading that way takes too long for the master so I build a > new > server instead. So, if I shutdown both postgresql instances old and > new, > rsync the data directory and restart on the new. I should be OK ? > Should be, yeah.
Re: Moving the master to a new server
On Tue, 2022-02-15 at 08:29 +1300, Glen Eustace wrote: > I need to move my master postgresql deployment to a new server. > > I am comfortable with stopping all connections then doing a > pg_dumpall > > psql to move the databases, they are not huge so this completes in > an > acceptable time and I am not expecting any data loss but I am > unsure of > what impact this will have on the streaming replication. I will be > rebooting the new server with the old servers network configuration > so I > am hoping that when I let connections back in, replication will > just > restart but I cant find any documentation that says so. > pg_dump -> restore will break your streaming replication. You'll need to set it up again. If the PG version isn't changing and you're still on the same version of Linux, rsync would be easier.
Re: Find missing data in a column
On Wed, 2021-12-29 at 12:43 -0500, john polo wrote: > I have a database in PostgreSQL 12 on Windows. It has > 8,000,000 > rows. > I want to copy this database to PostgreSQL 10 on Slackware Linux. I > used > this command to get the data out of the Windows database: > > "C:\Program Files\PostgreSQL\12\bin\pg_dump.exe" > -- > file="C:\Users\Nightrunner\DOCUMENTS\programming\pg_out\ebird_sptl_ > all.dump" > --host="localhost" --port="5432" --username="postgres" --password > --verbose --format=c --no-owner --no-privileges -- > dbname="ebird_work" > --table="p_loc.ebd_sptl" > > On Slackware, I first: > > su postgres > > Then try: > > psql ebirds > > SET SEARCH_PATH TO p_loc; > > COPY p_loc.ebird_sptl FROM > '/nt-d/projects_and_data/ebd_OR/ebird_sptl_all.dump'; > > That fails with > > missing data for column "COMMON_NAME" > > I understand this means there is a problem with one or more rows in > the > column referenced. This is a column of text. How do I find the > error(s) > in question? You're going to want to look into the pg_restore command to restore a custom format dump file. If you wanted something to read with COPY FROM you would first dump it with COPY TO.
Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
On Mon, 2021-12-06 at 10:19 -0700, Rob Sargent wrote: > To be clear, is it the devs or the ORM that's adding the ORDER and > the > LIMIT? I'm betting on devs. Do they need the smallest id (first > occurrance?) or do they need data common to all 5096 entries > (Name?) and > any record will do?. For the former they might be better off > asking for > just the attributes they need and for the latter you need to > provide an > option which gets them that single record. Of course, If they have > the > "smallest id" in hand they should request that. That assumes I could figure what bit of ORM code is generating this, talk to them, and then get them to actually think about what data they're looking for and it's impact on the database. :/ Given my 25 year track record with devs, I'm thinking of that as plan B. Hopefully though if they're looking for something common to all the records they would look at the parent table instead. I do expect the dev actually specified the order/limit for some reason. Thank you for the suggestions.
Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
On Mon, 2021-12-06 at 18:20 +0100, Francisco Olarte wrote: > > Can you post an explain analyze? To me it seems like the planner > thinks shipment_import_id is randomly distributed and the table is > well correlated with it's PK, so scanning it for the first id > should > be fast. #explain analyze SELECT "shipment_import_records".* FROM shipment_import_records" WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; -- --- -- --- - Limit (cost=0.44..873.08 rows=1 width=243) (actual time=31689.725..31689.726 rows=1 loops=1) -> Index Scan using shipment_import_records_pkey on shipment_import_records (cost=0.44..5122405.71 rows=5870 width=243) (actual time=31689.723..31689.724 rows=1 loops=1) Filter: (shipment_import_id = 5090609) Rows Removed by Filter: 28710802 Planning Time: 0.994 ms Execution Time: 31689.744 ms (6 rows) The biggest one (but yes "earlier"): # explain analyze SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE "shipment_import_records"."shipment_import_id" = 1247888 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; QUERY PLAN - - Limit (cost=0.44..426.59 rows=1 width=243) (actual time=8007.069..8007.070 rows=1 loops=1) -> Index Scan using shipment_import_records_pkey on shipment_import_records (cost=0.44..5126628.40 rows=12030 width=243) (actual time=8007.068..8007.068 rows=1 l oops=1) Filter: (shipment_import_id = 1247888) Rows Removed by Filter: 10929193 Planning Time: 0.584 ms Execution Time: 8007.086 ms (6 rows) And the smallest/latest, which actually uses the "right" index: # explain analyze SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE "shipment_import_records"."shipment_import_id" = 5116174 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; QUERY PLAN - - Limit (cost=145.44..145.44 rows=1 width=243) (actual time=0.018..0.018 rows=1 loops=1) -> Sort (cost=145.44..145.64 rows=79 width=243) (actual time=0.017..0.018 rows=1 loops=1) Sort Key: id Sort Method: quicksort Memory: 26kB -> Index Scan using index_shipment_import_records_on_shipment_import_id on shipment_import_records (cost=0.44..145.05 rows=79 width=243) (actual time=0.013 ..0.014 rows=1 loops=1) Index Cond: (shipment_import_id = 5116174) Planning Time: 0.104 ms Execution Time: 0.032 ms (8 rows) > > But from the names of the field you may have correlation between > shipment_import_id and id hidden somewhere ( like they are two > serial > growing together, you query for the latest shipment ids and it > scans > all the table ). An explain analyze should show that ( or three, > one > for that shipment import id, one for 1, one for a really big one ) This is definitely the case. And we are generally looking for newer data for most operations. Thanks for looking at it.
Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
On Mon, 2021-12-06 at 10:18 -0700, Michael Lewis wrote: > What is your default_statistics_target and how accurate is that > estimate of 5668 rows? What is random_page_cost set to by the way? > > > default_statistics_target = 1000 random_page_cost = 2.0 (it's on AWS on a 9000 iops gp2 volume) Postgresql 13.5 btw. The estimate was reasonably accurate, there were 5069 actual rows matching. > More importantly, what is the better plan that you'd like the planner > to use with your existing indexes? Well, it takes a few ms to grab all 5000 rows by shipment_import_id and then sort/limit them. It takes 30 seconds to do what it is doing instead, and only when the table is mostly cached already, more like 4-5 minutes otherwise. #explain analyze SELECT "shipment_import_records".* FROM shipment_import_records" WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; - - - Limit (cost=0.44..873.08 rows=1 width=243) (actual time=31689.725..31689.726 rows=1 loops=1) -> Index Scan using shipment_import_records_pkey on shipment_import_records (cost=0.44..5122405.71 rows=5870 width=243) (actual time=31689.723..31689.724 rows=1 loops=1) Filter: (shipment_import_id = 5090609) Rows Removed by Filter: 28710802 Planning Time: 0.994 ms Execution Time: 31689.744 ms (6 rows) Just with a kludge to force the better index: # explain analyze SELECT * FROM (SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE "shipment_import_records"."shipment_import_id" = 5090609 OFFSET 0) AS x ORDER BY "id" ASC LIMIT 1; QUERY PLAN - - -- Limit (cost=10655.34..10655.34 rows=1 width=243) (actual time=4.868..4.869 rows=1 loops=1) -> Sort (cost=10655.34..10670.02 rows=5870 width=243) (actual time=4.867..4.868 rows=1 loops=1) Sort Key: shipment_import_records.id Sort Method: top-N heapsort Memory: 27kB -> Index Scan using index_shipment_import_records_on_shipment_import_id on shipment_import_records (cost=0.44..10567.29 rows=5870 width=243) (actual time=0.037..3.560 rows=5069 loops=1) Index Cond: (shipment_import_id = 5090609) Planning Time: 0.135 ms Execution Time: 4.885 ms (8 rows) > > Certainly a composite index would be very helpful here. Using explain > analyze and sharing the output would give more info to go on. > Yeah I am going to just do the composite index for now, but was hoping for a more generic option. Thanks for looking at it.
Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT
I keep running into problems like these: Devs are using an ORM. It really likes to produce queries like: SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; I don't know why they do this. Usually it's more like 50 for pagination which make more sense. But for whatever reason this keeps coming up. The table has nearly 29 million records. 5069 of them match shipment_import_id = 5090609. There is an index on shipment_import_id, which the planner happily uses without the LIMIT specifically. Yet with it the query planner will always do something like: # explain SELECT "shipment_import_records".* FROM "shipment_import_records" WHERE "shipment_import_records"."shipment_import_id" = 5090609 ORDER BY "shipment_import_records"."id" ASC LIMIT 1; QUERY PLAN - Limit (cost=0.44..873.35 rows=1 width=243) -> Index Scan using shipment_import_records_pkey on shipment_import_records (cost=0.44..517.70 rows=5868 width=243) Filter: (shipment_import_id = 5090609) .. which takes minutes. I know I can work around this. Generally I would just drop the index on shipment_import_id and create one on shipment_import_id,id. Or if I can get the devs to wrap their query in an inner select with a fake offset to fool the query planner that works too. But both seem hacky. Just wondering if there's a knob I can turn to make these more likely to work without constantly implementing workarounds? Thanks for any help.
Re: Psql wants to use IP6 when connecting to self using tcp...
On Wed, 2021-06-23 at 17:25 -0500, Jerry LeVan wrote: > > So the question is: Why does using the short name evidently cause postresql > to use the ipv6 address > and using the full name use the ipv4 address? I'm thinking this might be coming from Avahi, which might be enabled on Fedora by default. That uses local network broadcasts for name discovery, and is the only thing I can think of that would result in an fe80:: address showing up in a name search if you didn't deliberately add it to DNS. Check /etc/nsswitch.conf and remove anything like mdns4_minimal. https://fedoramagazine.org/find-systems-easily-lan-mdns/ signature.asc Description: This is a digitally signed message part
Re: Database issues when adding GUI
On Mon, 2021-06-07 at 09:22 -0700, Rich Shepard wrote: > On Mon, 7 Jun 2021, Edson Carlos Ericksson Richter wrote: > > > Are you sure it should be 127.0.1.1, not 127.0.0.1? AFAIK, localhost > > should be 127.0.0.1 > > May be an issue in /etc/hosts for "salmo" host? > > Edson, > > salmo, 127.0.0.1 is the server/workstation that has everything installed. It > is localhost. > 127.0.0.1 localhost.localdomain localhost > 127.0.1.1 salmo.appl-ecosys.com salmo # for slrn Yeah that's your problem. PostgreSQL isn't going to be listening on 127.0.1.1 Good catch, Edson.
Re: Database issues when adding GUI
On Mon, 2021-06-07 at 09:03 -0700, Rich Shepard wrote: > The problem source is postgres telling me it cannot connect to the database > but I can do so directly using psql: > > $ psql --host salmo --user rshepard --dbname bustrac > psql: error: could not connect to server: could not connect to server: > Connection refused > Is the server running on host "salmo" (127.0.1.1) and accepting > TCP/IP connections on port 5432? > > yet, > > $ psql bustrac > psql (12.2) > Type "help" for help. > > bustrac=# > > I'm thoroughly confused not before encountering this issue. If you don't specify a host name, psql/libpq connects using the UNIX domain socket in /tmp. If you do specify a host name it connects using a TCP socket. Your PostgreSQL doesn't seem to be listening on TCP, or possibly you have a firewall issue.
Re: archive_commnad parameter question
On Mon, 2021-04-19 at 21:09 +, Allie Crawford wrote: > Hello, > I am new in PostgreSQL and I am trying to understand what the “test” word is > representing in the archive_command configuration that the PostgreSQL > documentation is showing as the format on how to set up this parameter > > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p > /mnt/server/archivedir/%f' # Unix > > Does anybody know what is “test” representing in this parameter > configuration? 'test' in this case is an actual executable present on many Unix and Unix-like systems. In this case it effectively gates the copy (cp) command so that it only runs if the target file does not already exist.
Re: PostgreSQL 11 with SSL on Linux
On Thu, 2020-06-04 at 17:32 +, Susan Joseph wrote: > So when I run rpm -i it says that the package is installed, but I > can't find a postgresql directory with all the files and executables. > So what am I missing? > > > > > > The server stuff is in postgresql11-serverif you're using the community rpms.
Re: AW: Linux Update Experience
On Thu, 2020-05-28 at 09:00 +, Marco Lechner wrote: > Hi Markus, > > at the moment we are facing similar conflicts on Oracle LInux 7 (wich > is derived from RHEL) – we manage our machines using Spacewalk. The > conflicts occur (as expected) on Spacewalk as well as on manually > using yum: > > Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64 > (oraclelinux7postgresql11) > Benötigt: llvm-toolset-7-clang >= 4.0.1 FWIW, postgresql-devel can't be updated on CentOS 7 currently either. The 12.2 packages were fine but I have not been able to update to 12.3. Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 (pg12) Requires: llvm-toolset-7-clang >= 4.0.1 That llvm-toolset-7-clang dependency is not present in the CentOS, EPEL or PostgreSQL repos.
Re: Advise on how to install pl/perl on existing DB.
On Tue, 2020-05-26 at 12:32 -0400, David Gauthier wrote: > psql (9.6.0, server 11.3) linux > > Hi: > I'm a PG users who has asked our IT team to install pl/perlu on an > existing 9.6.0 instance on linux. They really don't know how to > approach this. Could someone point me to a good step-by-step > (including ptrs to any downloads they may need) ? > > Also, when they do this, will it require DB downtime ? > > Thanks in Advance ! a) that says your server is running 11.3, not 9.6 (you might want to update your own machine). b) That really depends on how it was installed. If they used a normal package repo they'll just want to install the postgresql11-plperl package or equivalent, and then restart PostgreSQL (although I'm not actually sure the restart is required?) and then do a "CREATE EXTENSION plperlu;" in your database. If they did some kind of custom installation then they might need to just figure it out.
Re: Best way to use trigger to email a report ?
On Fri, 2020-05-08 at 12:26 -0400, David Gauthier wrote: > psql (9.6.0, server 11.3) on linux > > Looking for ideas. I want a trigger to... > 1) compose an html report based on DB content > 2) email the report to a dist list (dl = value of a table column) > > If this will involve hybrid coding, I prefer PL/perl. The linux env > has both "mail" and "mutt" (if this is of any help). > plperlu can access resources outside PostgreSQL. I'd suggest using Net::Mail and Net::SMTP to compose and send the mail to localhost:25. This avoids any shell escaping issues and still lets your local MTA accept the mail immediately and deliver it when it can.
Re: Memory footprint diff between 9.5 and 12
On Thu, 2020-05-07 at 13:33 -0700, Tory M Blue wrote: > I hadn't noticed this until today, but a running 9.5 system with > buffers at 10GB starts and has been running years without issues. > (15GB available) > Postgres 12 will not start with that configuration, complaining about > memory availability. So Postgres12 won't start until shared buffers > is 6GB, but even with that, my DB servers , postgres queries started > complaining about being unable to allocate memory "unable to > allocate". > > So dropping them to 4GB (on a 15GB system), may help, but did I miss a > huge note about significant memory changes between 9.5 to 12? > > Is there something else I'm missing that on busy systems is important, > something introduced in 10 or 11 as again I'm not seeing anything > noted in 12. > Thanks Is this running on an otherwise identical system? Or do you have a different kernel, overcommit settings, or swap configuration?
Re: keeping images in a bytea field on AWS RDS
On Tue, 2020-03-31 at 15:49 -0400, Richard Bernstein wrote: > I am using postgresql on RDS. I need to upload an image to the table. > I understand that I need to set the PGDATA directory and place the > image file in it, before setting the path in the bytea field. But how > do I set PGDATA if I don't have the ability to set an > environment variable, and don't have access to the following on an AWS > controlled installation? You don't put a file path into a bytea field or do anything with the database filesystem. You insert the contents of the actual file into the field using SQL commands.
Re: trouble making PG use my Perl
On Mon, 2020-03-02 at 18:23 -0500, Tom Lane wrote: > Kevin Brannen writes: > > On Centos 6.10, it ships with Perl 5.10.1, which is really ancient > > tome. > > Well, yeah, because RHEL 6/Centos 6 are really ancient. That's > whatI'd expect with a long-term-support distro that's nearly > EOL.Replacing its Perl version would go against the whole point ofan > LTS distro. > > Centos 8 ships with 5.14 (IIRC). > > I don't have an actual Centos 8 machine handy to disprove that,but the > info I have says that RHEL8/Centos 8 branched off fromFedora 28, and > F28 most definitely shipped with Perl 5.26.Looking at their git repo, > the last few Fedora releasesshipped with I can confirm that CentOS 8 has perl 5.26.3.
Re: UPDATE many records
On Tue, 2020-01-07 at 11:58 -0900, Israel Brewster wrote: > > > Really? Why? With the update I am only changing data - I’m not adding > any additional data, so the total size should stay the same, right? > I’m obviously missing something… :-) > PostgreSQL keeps the old row until it gets vacuumed, as it needs to be visible to other transactions. Not only that, but every index record gets updated to point to the location of the new data row too (excluding HOT), and those old index blocks also need to get vacuumed. And none of those rows can get removed until your update finishes. I know this isn't universally true with HOT and fillfactor etc. but with an update this big I think it's safe to say most of the space will get doubled. Plus you'll get a ton of write-ahead logs.
Re: UPDATE many records
On Tue, 2020-01-07 at 11:47 -0900, Israel Brewster wrote: > One potential issue I just thought of with this approach: disk space. > Will I be doubling the amount of space used while both tables exist? > If so, that would prevent this from working - I don’t have that much > space available at the moment. The original update you planned would do that, too. You probably need to just do the update in batches and vacuum the table between batches.
Re: migration from 9.4 to 9.6
On Fri, 2019-12-06 at 21:38 +, Julie Nishimura wrote: > I'd like to copy one single database from 9.4 cluster to a new 9.6 > cluster (migration with the upgrade), to the different host > > > > > > > > > > Put 9.4 on the new server. Replicate the db to it. When you're ready to switch, shut down the master, promote the new db, and then shut it down and pg_upgrade -k it to 9.6. That does require the binaries from both versions to be on the new server for a while, but it'll give you the least downtime and it's a very simple replication setup.
Re: Postgres Point in time Recovery (PITR),
On Mon, 2019-10-21 at 20:40 +0530, Avinash Kumar wrote: > can't he destroy the offline backups and your database ? > This is not a right justification to encouraging Offline Backups over > Online Backups. > If you are worried about storing your online backups through internet > on cloud (i do not agree as you can still secure your data on cloud), > store it in on a server in your Organizational network and do not push > them through internet. > Taking Offline Backups is not the only right way to ensure Reliable > Backups. > We are way ahead of the days where you need to face downtime to take > backups. > Online Backups are reliable in PostgreSQL. I apologize, I think we have a difference in terminology here. Obviously you don't need to take PostgreSQL out of service to take a backup. I don't know that you ever did; pg_dump even has always worked fine while the database is available. When I say offline backup I mean a backup that is stored in a way that it cannot be accessed via the Internet.
Re: Postgres Point in time Recovery (PITR),
On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote: > We need to ensure that we have safe backup locations, for example, > push them to AWS S3 and forget about redundancy. > Why do you think only Offline Backups are reliable today ? There have been examples of hackers gaining control of an organization's servers or cloud accounts and not only destroying their online systems but also methodically deleting all their backups. There are fewer things that can go catastrophically wrong if one has actual offline backups. You have to be a lot more careful about protecting anything attached to the Internet.
Re: Text search lexer's handling of hyphens and negatives
On Tue, 2019-10-15 at 20:34 -0700, raylu wrote: > On Tue, Oct 15, 2019 at 3:35 PM Alan Hodgson > wrote: > > My company has found the pg_trm extension to be more useful for > > partial text searches than the full text functions. I don't know > > specifically how it might help with your hyphens but it would be > > worth testing. The docs actually suggest using them in conjunction > > in some cases. > > We actually do use pg_trgm already for the names/titles of > things.Indexing the content with a trigram index and then > doingLOWER(content) LIKE '%789-xyz%' would certainly work, but1. we'd > have to do a little bit of finagling if we wanted to match onword > boundaries (don't match '6789-xyza' in the above example)2. trigram > indexes are pretty huge for long documents, which is why wecurrently > only use them for names/titles > We may give up and just use pg_trgm for contents if nothing else > worksout but it feels like the text search lexer is _so_ close to what > wewant. Maybe you could have a trigger pull out those specific hypenated references into a separate column when the document is added or updated, and store/index those separately?
Re: Is my lecturer wrong about PostgreSQL? I think he is!
Assuming you're not a troll ... On Wed, 2019-10-09 at 20:06 +0100, Pól Ua Laoínecháin wrote: > 1) Is my lecturer full of it or does he really have a point? > He's more than full of it. PostgreSQL has had a few bugs over the year that could have resulted in data corruption, but they're pretty rare and fixed as soon as they're found. PostgreSQL is the most reliable software I run, and virtually the only major piece I don't hesitate to upgrade without waiting to see what bugs other people find first. > > 4) What is the OS of choice for *_serious_* PostgreSQL installations? That's a religious question, not a technical question. I think even Microsoft makes a decent server OS nowadays. But I expect a large majority of PostgreSQL installations are running on Linux, as are the vast majority of all server apps nowadays. Having said that, I don't run a "serious" PostgreSQL installation; some of the people here run databases that do tens of thousands of TPS and hold many TiB of data. You'd have to ask them I guess.
Re: How to run a task continuously in the background
On Thu, 2019-07-11 at 11:19 -0500, Michael Nolan wrote: > A cron job will only run once a minute, not wake up every second. > But you could write a PHP program that does a one-second sleep before > checking if there's something to do, and a batch job that runs > periodically to see if the PHP program is running, and if not, launch > it. > > That's how I handle a job that opens a tunnel from our PostgreSQL > server to a MySQL server running at AWS so we can synchronize data > between those two database servers. It dies periodically for reasons > we've never figured out, so every 5 minutes I check to make sure it's > running. If you run such a job under systemd you can tell systemd to automatically restart it if it dies. Alternate, the application monit is an older means of monitoring and restarting persistent processes, complete with email notifications. This is quite a common system administration task. No need to roll your own.
Re: running out of disk space
On Thu, 2019-05-09 at 16:49 +, Julie Nishimura wrote:
> Alan, thanks for your reply.
>
>
>
>
>
>
>
> So, we currently have this situation:
>
>
>
>
>
>
>
> /dev/sda1 2.7T 2.4T 298G 90% /data/vol1
>
>
> /dev/sdb2 2.7T 2.4T 296G 89% /data/vol2
>
>
>
> /dev/mapper/vg00-data
>
>
>
>16T 16T 373G 98% /data/vol3
>
>
>
>
>
>
>
> postgres=# SELECT * FROM pg_tablespace;
>
>
> spcname | spcowner | spclocation | spcacl
>
>
>
>
> +--+-+---
>
>
>
>
> pg_default | 10 | |
>
>
>
> pg_global | 10 | |
>
>
>
> vol1 | 10 | /data/vol1 |
> {postgres=C/postgres,=C/postgres}
>
>
>
> vol2 | 10 | /data/vol2 |
> {postgres=C/postgres,=C/postgres}
>
>
>
> vol3 | 10 | /data/vol3 |
> {postgres=C/postgres,=C/postgres}
>
>
>
> (5 rows)
>
>
>
>
>
>
>
>
>
> And we added new volume yday:
>
>
>
>
> /dev/mapper/vg00-vol4
>
> 4.0T 195M 3.8T 1% /data/vol4
>
>
>
>
>
>
>
> So, if I understood you correctly, it is better to extend vol3
> instead of creating new tablespace on vol4 (if possible).
>
>
> If not, then what is my best bet?
>
>
>
Well you already have a mess of tablespaces, so I guess one more won't
hurt.
But since the new device is already in LVM and in the same volume group
it really would have been easier to grow vol3 instead of adding a vol4.
>
>
>
Re: running out of disk space
On Thu, 2019-05-09 at 15:46 +, Julie Nishimura wrote: > hello, > > We are running out of disk space, and we introduced new volume to it. > I am about to create new tablespace X and alter user databases to set > to this new tablespace X. So, all new tables will be created in X, > but what about existing tables on previous volume? > Its data will be split between 2 tbspaces? > > > > > > > > Please clarify. Thank you! > > > > Tables and indexes can only be on one tablespace. You'll need to individually move them with alter table and alter index. They will be locked while being moved. You should experiment with this on a test server to learn how it works. However, unless your new volume has different I/O characteristics than the existing volume you really shouldn't use tablespaces. You should use your OS volume manager and filesystem tools to extend the data volume and filesystem into the new space. This will be much easier to manage. I'm sure your system administrator can assist you with this.
Re: loading plpython error
> On 2/14/19 4:17 PM, Alan Nilsson wrote: > > Platform: Linux x86-64, CentOS 6, Postgres 11.1. > > > > We have installed from the YUM repo. The server runs fine but we > > are > > trying to add python support. > > > > yum install postrgesql11-contrib postgresql11-plpython > > > > I can see the installed files in the correct locations but when we > > invoke create extension, we are greeted with the following error: > > > > ERROR: could not load library "/usr/pgsql-11/lib/plpython2.so": > > /usr/pgsql-11/lib/plpython2.so: undefined symbol: getmissingattr > > > > Is there something yet to be installed? Is this a package error? > > Any > > hints to fixing? I see one possibly related issue talked about on a Debian list that extensions compiled against PostgreSQL 11.2 would likely not load into previous releases due to this missing symbol. https://bugs.launchpad.net/ubuntu/+source/skytools3/+bug/1815665 I would guess this is a related version mismatch; likely in the build/release process.
Re: Monitoring PITR recovery progress
On Wed, 2019-01-23 at 18:58 +0100, Ivan Voras wrote: > And, the actual question: how to monitor the WAL replay process? > Currently, the recovery.conf file is sitting there, with the database > running, but pg processes are idle, and pg_stat_activity doesn't list > anything which appears to be related to the recovery process. > > > The server logs each wal segment that gets processed during recovery. And you would definitely see a busy high-I/IO process applying the recovery. It also logs when the recovery is complete. And I'm pretty sure it renames recovery.conf to recovery.done or something when it's done.
Re: How to perform PITR when all of the logs won't fit on the drive
On Thu, 2018-03-01 at 17:28 -0500, Tony Sullivan wrote: Hello, I have a situation where something was deleted from a database that shouldn't have been so I am having to take a base backup and perform a point-in-time-recovery. The problem I have is that the decompressed WAL files will not fit on the drive of the machine I am trying to do the restore on. Your restore_command can be a complex command or shell script that can transfer WAL files as needed from a network source.
Re: On error mesage (0x80090325) whilst installing Apps Stack Builder
> > The problem is that I keep getting the following error mesage: > > - > > --- > > A certificate verification problem was encountered whilst accessing > > https//www.postgresql.org/applications-v2.xml > > schcannel: next InitializeSecurityContext failed: > > SEC_E_UNTRUSTED_ROOT (0x80090325) - The certificate chain was > > issued by an authority that is not trusted. > > This means that the source of the download cannot be verified. it > > is > > recommended that you do not continue with the download as it may > > be coming from a site that is preending to be the intended download > > site and may contain viruses or malware. > > > > Do you wish to continue? > > - > > -- > > I intend to continue, but there again I keep getting the following > > error message: > > - > > --- > > Couldn't access the URL > > 'https://www.postgresql.org/applications-v2.xml'. > > > > ERROR: schannel: next InitializeSecurityContext failed: > > SEC_E_UNTRUSTED_ROOT (0x80090325) - The certificate chain was > > issued by an authority that is not trusted. > > - > > -- > > I should be most grateful if any of you would provide me with any > > advice. > > Stack builder appears to be written in Java. You need a JDK newer than 8u101, that was the first version to include the LetsEncrypt root.
Re: postgres not starting
On Thu, 2018-02-15 at 18:21 -0600, Azimuddin Mohammed wrote: > Hello, > I am unable to start postgres on one of the server > I am getting below error "HINT: is another postmaster already running > on port 5432, if not wait a few seconds and retry" > I checked the processes nothing is running with postgres > > I think the error caused because I removed everything under /data > directory before stopping the server. > Can someone help. > > kill -9 the old processes. The old postmaster is holding onto the port. And probably some of the old file handles, too, so you might want to re-init the new database cluster after really cleaning up the old one.
Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu
On Thu, 2017-11-30 at 22:59 +, Ben Nachtrieb wrote: > Alan, > > > > Thank you! Solution: build them from source on the server? Well, it would be more maintainable to find a source for packages built for your particular OS. Or run a supported OS; that one looks pretty old. Or I guess you can build from source. > > >
Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu
On Thu, 2017-11-30 at 22:20 +, Ben Nachtrieb wrote: > > > ...to ld.so.conf, I get: > > ERROR: could not load library > "/var/lib/pgsql10/lib/postgresql/plperl.so": > /var/lib/pgsql10/lib/postgresql/plperl.so: undefined symbol: > Perl_xs_handshake > > SQL state: XX000 It looks to me like your plperl is built against a substantially different version of Perl than what you have installed on the server. I'd guess you installed a PostgreSQL binary built on a different OS version, or have otherwise mismatched packages. > >
