[GENERAL] Re-sync slave server

Hi guys, I'm using PostgreSQL 9.2 There is a backup server that is 4 days old replication by wal_files (not streaming) However, the server went down and I lost some wal_files.. and now the replication is not working... Question: Do I need to sync all the data folder from my master Or just

Re: [GENERAL] Re-sync slave server

My set up: Master --> slave01 (streaming replication) --> slave02 (streaming replication) Master --> slave03 (wal_files 4 days old, not streaming replication) The wal_files are stored into each server. But when the slave03 was down, the wal_files weren't being copied into it. We took too long to

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Ok so guys CREATE or REPLACE FUNCTION function_data_1() RETURNS SETOF bigint AS $$ declare row record; BEGIN [...] FOR row IN EXECUTE ' SELECT t1.file_id, t1.path, t1.account_id FROM

Re: [GENERAL] Re-sync slave server

yep.. pg_basebackup the problem is that the servers are in a different country.. and the DB is 2TB the pg_basebackup will probably fail and I'll have to do a DUMP as I've already done with another slave time ago

Re: [GENERAL] Re-sync slave server

Anyway.. will do the pg_basebackup and see how it goes... cheers guys Patrick 2016-06-08 8:41 GMT+12:00 Patrick B : > yep.. pg_basebackup the problem is that the servers are in a > different country.. and the DB is 2TB > > the pg_basebackup will probably fail and I'll hav

Re: [GENERAL] Re-sync slave server

One more question guys... Does the pg_basebackup re-write the data? or do I have to have free space? Like.. the DB is 2 TB.. do I have to have more 2TB? or it will use those 2TB that already are there? cheers

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Thanks Adrian... it's working ;)

Re: [GENERAL] Re-sync slave server

2016-06-08 11:03 GMT+12:00 Adrian Klaver : > On 06/07/2016 03:22 PM, Patrick B wrote: > >> One more question guys... >> >> Does the pg_basebackup re-write the data? or do I have to have free space? >> >> Like.. the DB is 2 TB.. do I have to have more 2TB? or i

[GENERAL] Automate copy - Postgres 9.2

Hi guys, I need to do a file backup for each account_id. Example: COPY (SELECT * FROM backup_table WHERE id = 1112 AND status = 1) TO '/var/lib/pgsql/1112.sql'; COPY (SELECT * FROM backup_table WHERE id = 1113 AND status = 1) TO '/var/lib/pgsql/1113.sql'; COPY (SELECT * FROM backup_table WHERE

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 12:19 GMT+12:00 Adrian Klaver : > On 06/08/2016 04:24 PM, Patrick B wrote: > >> Hi guys, >> >> I need to do a file backup for each account_id. >> >> Example: >> >> |COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus >> =1)TO&

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 12:13 GMT+12:00 John R Pierce : > On 6/8/2016 4:24 PM, Patrick B wrote: > > > I need to do a file backup for each account_id. > > Example: > > COPY (SELECT * FROM backup_table WHERE id = 1112 AND status = 1) TO > '/var/lib/pgsql/1112.sql'; > &g

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:15 GMT+12:00 John R Pierce : > On 6/8/2016 5:46 PM, Patrick B wrote: > > Single id as you show, a range of numbers or an array of numbers? > > > select function(21); > > Where 21 = Number of ids > > > how do you get the specific ID's from "

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:58 GMT+12:00 John R Pierce : > On 6/8/2016 6:47 PM, Patrick B wrote: > >> >> 21 is the number of IDS that I wanna perform that COPY command >> > > that didn't answer my question. if you call your function like SELECT > myfunction(21);

[GENERAL] Question about performance - Postgres 9.5

Hi guys, In the db I'm working one, it will be three tables: visits, work, others. Everything the customer do, will be logged. All inserts/updates/deletes will be logged. Option 1: Each table would have its own log table. visits_logs, work_logs, others_logs Option 2: All the logs would be stor

Re: [GENERAL] Question about performance - Postgres 9.5

Also... if something is changed inside the visits table (delete/insert/update), the visits_logs table will be logging the change. However, some joins between those three tables will become necessary in some point, as visits and works tables are related somehow...

Re: [GENERAL] Automate copy - Postgres 9.2

Hi guys, I created this function: CREATE or REPLACE FUNCTION function(account_id integer) RETURNS void AS $$ begin execute 'COPY ( SELECT * FROM backup_table WHERE account_id = ' || account_id || 'AND > status = 1 ) TO ''/var/lib/pgsql/'||account_id||'.sql'''; end $$ language 'plpgsql';

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-14 9:47 GMT+12:00 Adrian Klaver : > On 06/13/2016 02:42 PM, Patrick B wrote: > >> Hi guys, >> >> I created this function: >> >> CREATE or REPLACE FUNCTION function(account_id integer) >> >> RETURNS void AS $$ >>

Re: [GENERAL] Automate copy - Postgres 9.2

Yep... thanks Melvin.. It's working as expected now Cheers

Re: [GENERAL] Help on recovering my standby

I had the same issue... A slave server had missing wal_files... and it wasn't synced. I had to re-sync all the DB, by running the pg_basebackup command So.. basically, what I did is: 1 - Ensure that the wal_files are being inserted into the slave 2 - Backup the recovery.conf, postgresql.conf

[GENERAL] pg_archivecleanup - Increase time files are deleted

Hi guys, I've got one MASTER and three SLAVES servers, running PostgreSQL 9.2. I had a problem with one of the masters, and because the wal_files aren't kept for a long time, I had to re-sync all the DB. If the wal_files were being kept longer than they are now, I wouldn't have to re-sync (pg_bas

Re: [GENERAL] pg_archivecleanup - Increase time files are deleted

> > > > >...I'd like to have the wal_files stored for 24h, and then the > pg_archivecleanup could do its job and delete the files.. > > > *You cannot do that, but you can change the wal_keep_segments >

Re: [GENERAL] pg_archivecleanup - Increase time files are deleted

Apparently I can't do that via Postgres. So I've made a bash script to do the work for me. The script will only delete the wal_files older than 24h ( -mmin +1440 ) * It has been tested and it's working. *recovery.conf:* > archive_cleanup_command = 'exec nice -n 19 ionice -c 2 -n 7 > ../archiv

[GENERAL] wal_files from slave to another slave

Hi guys, can a slave server (streaming replication) send the wal_files to another slave, instead the master? If so, that would be done by setting the archive_mode = on and also a archive_command? I'm using PostgreSQL 9.2 Thanks! Patrick

Re: [GENERAL] wal_files from slave to another slave

Sorry guys.. just found the answer on the docs: https://www.postgresql.org/docs/9.2/static/warm-standby.html#Cascading-Replication (were trying to find it for a while lol) it is indeed possible to do that!

[GENERAL] Unable to recovery due missing wal_file

Hi guys, I'm currently using PostgreSQL 9.2. One of my backup servers went down, and I had to re-sync the all the DB. I used pg_basebackup and, of course, at the same time wal_archive. Steps: 1 - Confirm the wal_files are being copied into the new server. 2 - Delete /var/lib/pgsql/9.2/data/* 3

Re: [GENERAL] Unable to recovery due missing wal_file

One more question: Could I use pg_basebackup (or another tool like RSYNC) and re-sync the data folder only with the missing data? for example... incremental? So I wouldn't need to copy 2TB again?

Re: [GENERAL] Unable to recovery due missing wal_file

Yes, I read that! However, I store the wal_files manually into three different servers. I've double checked the files! I got over 500GB of wal_files when the pg_basebackup finished, and still, wasn't enough. I'll re-do the steps but now using the STREAM option.

Re: [GENERAL] Unable to recovery due missing wal_file

I don't have it now! But I didn't know that postgres would need that file! If I knew it, I'd have checked just after pg_basebackup started

Re: [GENERAL] Unable to recovery due missing wal_file

> > >> > Not sure that would have mattered for the reasons below. > > You might want to take a look at the below: > > https://www.postgresql.org/docs/9.5/static/wal.html > > In particular: > > https://www.postgresql.org/docs/9.5/static/wal-intro.html > > Short version WAL files are essential to res

Re: [GENERAL] Unable to recovery due missing wal_file

pg_basebackup --pgdata=- --format=tar --label=bkp_server --progress --host=localhost --port=5432 --username=replicator --xlog-method=stream Is that right? Once is finished, just need to restart postgres and set the recovery.conf.restored.command? Cheers

Re: [GENERAL] Unable to recovery due missing wal_file

*slave_new:* server that needs a new copy of the DB *slave01:* streaming replication slave *My steps are:* *1.* ssh slave_new *2.* Stop postgres *3.* rm -rf /var/lib/pgsql/9.2/data/* *4.* ssh postgres@slave01 'pg_basebackup --pgdata=- --format=tar --label=slave_new --progress --host=localhost --p

[GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

Hi guys, I got two slaves using Postgresql 9.2. slave01 - Streaming replication from master slave02 - nothing.. new server is it possible to get slave02 replicating from slave01 (Cascade streaming replication) this can be done with streaming ? and also slave01 sending the wal_files to that new s

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

Thanks! Someone from another list told me that wouldn't be possible on the 9.2 version, that's why I asked even that I read that doc... cheers

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

Yep.. thanks for the help!

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

> > > > On Thu, 7 Jul 2016, 9:20 a.m. Patrick B, wrote: > >> Hi guys, >> >> I got two slaves using Postgresql 9.2. >> >> slave01 - Streaming replication from master >> slave02 - nothing.. new server >> >> is it possible to get sla

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

ok.. cool... cheers mate! Patrick

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

> > > > a slave doesn't generate wal_files at all. > > if your master is keeping a wal_archive, slaves, including cascaded > streaming slaves, can all use that same archive as their wal source, these > are only used for catchup when streaming is interrupted. > > > so.. is or isn't possible a slave

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

ok... I'll archive the wal_files locally on slave01, and then send them to slave02 every 10 minutes, for example. This will be done by rsync or something else. I'll have still the problem with timeline, and slave02 won't be able to become a master in the future. Is that right?

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

The slave02 server will be a copy of the DB into Amazon. There will be a migration to Amazon in the future, and the company's managers want a copy of the current DB there to test and do all the stuffs they need (migrating to 9.5, too). slave01 is already working as a streaming replication server.

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

2016-07-07 14:55 GMT+12:00 Sameer Kumar : > > > On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, > wrote: > >> The slave02 server will be a copy of the DB into Amazon. There will be a >> migration to Amazon in the future, and the company's managers want a copy >>

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

2016-07-07 15:19 GMT+12:00 Sameer Kumar : > > > On Thu, Jul 7, 2016 at 11:02 AM Patrick B > wrote: > >> 2016-07-07 14:55 GMT+12:00 Sameer Kumar : >> >>> >>> >>> On Thu, 7 Jul 2016, 10:32 a.m. Patrick B, >>> wrote: >>> >

[GENERAL] Streaming replication failover process - Pgsql 9.2

Hi all, There will be a network maintenance at the company where my servers are... I've got one master and one slave server, running PostgreSQL 9.2. As the network will be down, the internet won't be working as well as the intranet. Both servers won't be able to communicate each other. Not by st

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

thanks guys.. thanks for all the comments... I'm not shipping the wal_files into master, I actually ship them into slave and another backup server as well. So I'll have to change my archive_command then :) Thanks!

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

archive_command = 'cp %p /var/lib/pgsql/archive/%f' That would be ok right guys? I will also setup wal_keep_segments to 512

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

2016-07-11 12:18 GMT+12:00 Adrian Klaver : > On 07/10/2016 04:28 PM, Patrick B wrote: > >> archive_command = 'cp %p /var/lib/pgsql/archive/%f' >> > > This would be where? > master server > > And does the corresponding restore_command point to

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

If the master server can't send the wal_files through the slaves, shouldn't the wal_files be in "background" waiting to be delivered? Otherwise what's the purpose of them? If a network fails I'd loose those files?

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

oh ok.. got it.. wal_keep_segments = To prevent the primary server from removing the WAL segments required for the standby server before shipping them, set the minimum number of segments retained in the pg_xlog directory so it would be ok just by increasing that parameter, right? Once the serve

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

2016-07-11 15:48 GMT+12:00 John R Pierce : > On 7/10/2016 4:28 PM, Patrick B wrote: > >> >> archive_command = 'cp %p /var/lib/pgsql/archive/%f' >> >> >> That would be ok right guys? >> >> > > normally, you want to ship your WAL archi

[GENERAL] Tracking IO Queries

Hi all, I got some IO spikes on my master server. But the point is that I was unable to find the query that caused that, because the query didn't take more than 300ms to run (300ms is the time that my alerts are settled)... Is there any way to track those queries? Maybe with pg_stat_statement? I

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

Hi guys, I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave. I ran this command, from the new_slave server. It will connects to my other slave and copy the DB. ssh postgres@slave05 'pg_basebackup --pgdata=- --format=tar > --label=new_slave --progres

Re: [GENERAL] Streaming replication failover process - Pgsql 9.2

sorry... wrong email. Will create a new topic.

Re: [GENERAL] Cascade streaming replication + wal_files - Pgsql 9.2

Hi guys,* (I'm sending this email here because it has the same subject, wal_files)* I'm setting up a new slave server, using Postgres 9.2. This new slave server I'll call: New_slave. I ran this command, from the new_slave server. It will connects to my other slave and copy the DB. ssh postgres@s

[GENERAL] Reset stats - Pg 9.2

Hi all, > > > select stats_reset from pg_stat_database; It says the statistics were reseted back in 2014. I want to reset it now (production database), to get more clear data. select pg_stat_reset(); Should be ok? Those stats aren't used for Query plan, right? Thanks Patrick

Re: [GENERAL] Uber migrated from Postgres to MySQL

We can't use the pg_upgrade in our 3TB database just does not work.. that's the main reason we're still using 9.2.

Re: [GENERAL] Uber migrated from Postgres to MySQL

> > I think it's safe to say that that has absolutely nothing to do > with the size being 3TB. They symptoms you report are a little > thin to diagnose the actual cause. might be... we're using SATA disks... and that's a big problem. But still.. the size of the DB is indeed a problem.

[GENERAL] pg_archivecleanup standalone bash script

Hi all, I want to hold the wal_files for 30 days on my slaves. For that reason, I created a script to do that: *pg_archivecleaup_mv.bash:* #!/bin/bash -eu declare -r -x PATH='/usr/local/bin:/usr/bin:/bin'; # We just wanna delete the wal_files older than 30 days ARCHIVEDIR='/var/lib/pgsql/9.

Re: [GENERAL] pg_archivecleanup standalone bash script

This has been resolved. cheers

[GENERAL] Postgresql 9.2 Ubuntu - is not starting

Hi guys, I've installed potgres on a Ubuntu machine. When I start it, it doesn't start... > /etc/init.d/postgresql status *●* postgresql.service - PostgreSQL RDBMS >Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor > preset: enabled) >Active: *active (exited)* sin

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

2016-08-03 8:13 GMT+12:00 Adrian Klaver : > On 08/02/2016 01:04 PM, Patrick B wrote: > >> Hi guys, >> >> I've installed potgres on a Ubuntu machine. >> > > Postgres version? > have a look on the subjec of this email (9.2) > > Ubuntu versio

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

So, I found a log file on /var/log/postgres/ DETAIL: "max_stack_depth" must not exceed 7680kB. > HINT: Increase the platform's stack depth limit via "ulimit -s" or local > equivalent. > 2016-08-02 19:54:06.293 UTC|12960|FATAL: configuration file > "/etc/postgresql/9.2/main/postgresql.conf" cont

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

> > > Short version: > > That probably should be postgresql not postgres > > Long version: > > https://help.ubuntu.com/community/PostgreSQL > > >> > Yep.. my mistake.. /etc/init.d/postgresql start/stop/restart

Re: [GENERAL] Postgresql 9.2 Ubuntu - is not starting

This is now solved guys. There were three problems. 1 - I was looking for the logs on /var/lib/postgresql/9.2/main/pg_log. And it's wrong. On ubuntu, the logs are: /var/log/postgresql 2 - I just changed the max_stack_depth to the default DETAIL: "max_stack_depth" must not exceed 7680kB. > HINT

Re: [GENERAL] pg_archivecleanup standalone bash script

I'll ajust the script and once is done will share here with u guys Patrick

[GENERAL] Question about wal files / pg_xlogs

Hi all, I'm currently using PostgreSQL 9.2. I noticed that the wal_files are being generated by the master well, no problems. But on the slaves, it seems to be a delay to the delivery of those wal_files. I got two slaves using streaming replication and wal files shipment from Master. *On the mas

Re: [GENERAL] Question about wal files / pg_xlogs

> > >> > Both machines have same timezone? > Yes! Shouldn't be showing 2 hours before.. I just checked and both server has the same date / timezone > > How fast are you generating WALs? > Check below please checkpoint_segments = 64 > checkpoint_timeout = 5min > checkpoint_completion_target =

Re: [GENERAL] Question about wal files / pg_xlogs

@Adrian, > Seems to me the settings for nice and ionice above would, on a busy > machine, slow down the transfer. Has there always been a notable time > difference in the transfer or has it gotten worse over time? > > Yep... I also thought about that. Specially because the master is constantly ge

Re: [GENERAL] Question about wal files / pg_xlogs

> > >> > https://www.postgresql.org/docs/9.5/static/continuous-archiv > ing.html#BACKUP-ARCHIVING-WAL > > "However, archive_command can be changed with a configuration file reload." > > Cheers... I removed the IONICE command from the archive_command. However, did not see any difference. Any ide

Re: [GENERAL] Question about wal files / pg_xlogs

> > >> > Just out of curiostity, are the slaves in the same physical location, or > by some chance are they on a remote site? > > > two of them in the same physical location, and the other two different country.

Re: [GENERAL] Question about wal files / pg_xlogs

> > > And the time difference is on ALL slaves, or just the two in a different > country? > > > All of them! :(

Re: [GENERAL] Tunning Server 9.1.

@John R Pirce, on a 128GB ram system, I would probably have shared_buffers up around 4-8GB. I'm using shared_buffer = 52GB in a 128GB RAM server :O

Re: [GENERAL] Question about wal files / pg_xlogs

Hi guys, I can now confirm that by taking off the IONICE command solved my problem. Thanks a lot ! Patrick

Re: [GENERAL] pg_archivecleanup standalone bash script

IR}) cd $ARCHIVEDIRexec "/usr/pgsql-9.2/bin/pg_archivecleanup" -d /var/lib/pgsql/archive ${LAST_BACKUP} | find $ARCHIVEDIR -type f -mmin +30 -delete 2016-08-03 8:32 GMT+12:00 Patrick B : > I'll ajust the script and once is done will share here with u guys > > > Patrick >

[GENERAL] permissions PostgreSQL 9.5

hi guys, just setting up a new DB using PostgreSQL 9.5. I've created a new username for the code, called codeuser. To give the username access to all the tables, views, etc I ran: GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO > codeuser; Is that ok? Is that enough?

[GENERAL] PK Index - Removal

Hi guys, I got the following index: CREATE INDEX "ix_mo_pk" ON "mo" USING "btree" ((("id")::"text")) The "ID" is my primary key: > "mo_pkey" PRIMARY KEY, "btree" ("id") The ix_mo_pk index is not being used... But as it has the "ID" on it, I wanted to ask before dropping it. Can you guys ex

Re: [GENERAL] PK Index - Removal

Cool... Thanks guys! Index removed. Patrick

[GENERAL] Running pg_dump from a slave server

Hi guys, I'm using PostgreSQL 9.2 and I got one master and one slave with streaming replication. Currently, I got a backup script that runs daily from the master, it generates a dump file with 30GB of data. I changed the script to start running from the slave instead the master, and I'm getting

Re: [GENERAL] Running pg_dump from a slave server

2016-08-17 15:31 GMT+12:00 Sameer Kumar : > > > On Wed, Aug 17, 2016 at 10:34 AM Patrick B > wrote: > >> Hi guys, >> >> I'm using PostgreSQL 9.2 and I got one master and one slave with >> streaming replication. >> >> Currently, I

Re: [GENERAL] Running pg_dump from a slave server

> > > > But do you have statements which causes Exclusive Locks? Ignoring them in > OLTP won't make your life any easier. > > (Keeping avoiding to get into 'recovery conflict' as your sole goal) If > you decide to run pg_dump from master, it would block such statements > which have Exclusive locki

[GENERAL] Permissions pg_dump / import

Hi guys, I'm running a pg_dump and then importing the dump into a test server. I'm using PostgreSQL 9.5. *pg_dump:* > pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v > -f test1_NEW.sql *Steps into the new database (test1):* > CREATE SCHEMA public; > GRANT ALL ON SCHEM

Re: [GENERAL] Permissions pg_dump / import

2016-08-18 10:30 GMT+12:00 Ilya Kazakevich : > >> Owned by: public.accounts.id > > This is not owner but table this sequence depends on. See > http://stackoverflow.com/questions/6941043/get-table- > and-column-owning-a-sequence > > > > Use query provided on SO to get real owner > > > > > Thanks g

[GENERAL] PGPASSWORD - More than one in a bash script

Hi guys, I'm writing a bash script to dump and restore (pg_dump + pg_restore) a test database. However, the username to access the pg_dump server is different of the one to access pg_restore. I'm using the PGPASSWORD parameter on the script, but can I put two of that? like: > PGPASSWORD > PGPA

Re: [GENERAL] Permissions pg_dump / import

Hi guys, I'm doing a pg_dump and a pg_restore on the same command, using different usernames and databases names.: pg_dump --format=custom -v --no-password --no-owner --username=teste1 > --dbname=test1 --host=11.11.11.11 | pg_restore -v --schema=public > --no-password --no-owner --username=maste

[GENERAL] Vacuum Full - Questions

Hi guys, A dev has ran a VACUUM FULL command into our test database running PostgreSQL 9.5 (I know... goddamn)... ... after the Vacuum Full, some queries start using SEQ scans instead of indexes... Does that happen because of the size of the table? The table that I'm referring to is 150MB bi

[GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

Hi guys, I posted this question on the ADMIN list but will post here as well so more people can comment... https://www.postgresql.org/message-id/CAJNY3it_AfxJhmwMHtpiAbHG47GS5rJOAUgfHw%2BGm5OXCbUm7w%40mail.gmail.com I've got a 2.3TB Database running at Rackspace... We'll be migrating it to RDS Pos

Re: [GENERAL] Vacuum Full - Questions

2016-09-01 11:53 GMT+12:00 Venkata B Nagothi : > > On Thu, Sep 1, 2016 at 8:41 AM, Patrick B > wrote: > >> Hi guys, >> >> A dev has ran a VACUUM FULL command into our test database running >> PostgreSQL 9.5 (I know... goddamn)... >> >> ... aft

Re: [GENERAL] Rackspace to RDS using DMS (Postgres 9.2)

> > > > It is 2.3TB, it is going to take a long time no matter what service you > are running. > > I know that Joshua! I just wanna get an idea from someone who has used DMS service... > > > No, I have not used DMS. Frankly, with all respect to AWS/RDS the idea of > running a 2.3TB instance that

[GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

Hi guys, I'll be performing a migration on my production master database server, which is running PostgreSQL 9.2 atm, from SATA disks to SSD disks. I've got some questions about it, and it would be nice if u guys could share your experiences/thoughts: *SCENARIO:* I currently have one MASTER and

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-02 15:36 GMT+12:00 Venkata B Nagothi : > > On Fri, Sep 2, 2016 at 12:48 PM, Patrick B > wrote: > >> Hi guys, >> >> I'll be performing a migration on my production master database server, >> which is running PostgreSQL 9.2 atm, from SATA disks

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

Hi guys, You can actually reduce the time more by pre-syncing to the new location. something like: rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ service postgres stop rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ The second rsync will only copy the deltas from the first, it still has to > go in an

[GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

Hi guys, I got the tasks table that has the tasks_start column: > tasks_start| timestamp(3) without time zone select tasks_start from tasks LIMIT 1; > tasks_start > --- > 2016-08-10 00:30:00 I'm trying to cast the date, using this query: > SELECT cast(tasks_start a

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

> > > You might want to share the version of PostgreSQL you are using. > > You might want to try date_trunc and AT TIMEZONE function/operators- > > https://www.postgresql.org/docs/9.4/static/functions- > datetime.html#FUNCTIONS-DATETIME-TRUNC > > SELECT date_trunc('day', tasks_start at TIME ZONE 'E

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

Hi guys, You were right, there was something wrong with my original query: SELECT cast(tasks_start as date) FROM "tasks" WHERE (Date(tasks_start) in > ('2016-08-10'); I was able to get the expected data using the above query... Cheers Patrick

[GENERAL] Help with slow query - Pgsql 9.2

Hi guys, I got this query: > SELECT id,jobid,description,serialised_data > FROM logtable > WHERE log_type = 45 > AND clientid = 24011 > ORDER BY gtime desc Explain analyze: https://explain.depesz.com/s/XKtU So it seems the very slow part is into: -> Bitmap Index Scan on "ix_cl

[GENERAL] Postgres UPGRADE from 9.2 to 9.4

Hi guys, I'll be migrating my Postgres 9.2 database from Rackspace to Amazon. To do that work, I'll be using DMS at amazon... unfortunately DMS needs a Postgres 9.4+ version at least ( http://docs.aws.amazon.com/pt_br/dms/latest/userguide/CHAP_Introduction.Sources.html) - So that means that I'll

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

2016-09-07 11:25 GMT+12:00 John R Pierce : > On 9/6/2016 4:20 PM, Melvin Davidson wrote: > >> If you use slony to replicate, you CAN have 9.2 on the master and 9.4 on >> the slave. >> > > does rackspace support slony? how about amazon dms ? > > slony requires configuring replication on each table

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > John, > Yes, Rackspace supports slony. I used to work there and they use slony on > their core system. > > Patrick, > You cannot stream directly from 9.2 to 9.4. However, you can just create a > separate 9.4 cluster (on a separate port). > Then pg_dump from 9.2 in plain format and it will loa

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > how large is the full database cluster? > > if its only a few GB or whatever, and not grossly complex, pg_dumpall -h > oldhost | psql -h newhost, is the simplest way to move a complete set of > databases from an old server to a new. if there are large tables with > millions of rows indexed, m

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > Or if you want, upgrade on your existing provider first, then setup > streaming replication. But no matter what, pg_upgrade will require some > form of downtime. > > You could also use either Slony or londiste to directly migrate from > Rackspace to an EC2 instance on 9.5; no need for DMS at

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-08 11:49 GMT+12:00 Jim Nasby : > Please include the mailing list in replies... > > On 9/7/16 6:10 PM, David Gibbons wrote: > >> That is NOT safe. The problem is it allows rsync to use mtime alone >> to decide that a file is in sync, and that will fail if Postgres >> writes to a

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-11 14:09 GMT+12:00 Jim Nasby : > On 9/8/16 3:29 PM, David Gibbons wrote: > >> >> Isn't this heading in the wrong direction? We need to be more >> precise than 0 (since 0 is computed off of rounded/truncated time >> stamps), not less precise than 0. >> >> Cheers, >> >>

  1   2   3   >