Recover databases from raw files (only exists the base directory)
Hi. I have been contacted for this interesting issue: on a standalone postgresql 9.4 server someone deleted all PostgreSQL files and directories except the base directory and its content (/var/lib/pgsql/9.4-base/data/base). So, the question is: there is any chance/procedure to recover the databases? As usual, the last backup has "a few" weeks. So, this dump can be used to recover the recent data? Thanks a lot for you help. Carlos MartÃnez.
Re: Table and Index bloating
Hi. You can consider use pg_repack to remove bloat (http://reorg.github.io/pg_repack/) Best regards. Carlos Martinez On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot wrote: > Hi, > > You can track that with some monitoring tools like check_pgactivity. The > queries can be found here : > https://github.com/ioguix/pgsql-bloat-estimation. > > Tables : > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql > Indexes : > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql > > Backups should also be part of your maintenance plan. > Vacuum/Analyse/Reindex is already a good basis. > > > Kind regards > > > On 11/27/2017 10:10 AM, Vikas Sharma wrote: >> Hi All, >> >> Could someone please provide the query/queries to find table and >> Index bloating in PgSql 9.3 onwards? >> and are there any other maintenance tasks inPgSql except vacuum & analyse. >> >> Regards >> Vikas > > -- > Stefan FERCOT > http://dalibo.com - http://dalibo.org > >
Re: Table and Index bloating
Hi. Can we use pg_repack with standard community edition of postgresql? Yes. and can it be used on the databases with streaming replication? Yes. Best regards. Carlos Martinez On Mon, Nov 27, 2017 at 10:08 AM, Vikas Sharma wrote: > Thank you Stefan for the queries. > > Thanks you Carlos for pg_repack suggestion, Can we use pg_repack with > standard community edition of postgresql? and can it be used on the > databases with streaming replication? > > Regards > Vikas Sharma > > On 27 November 2017 at 14:58, Carlos Martinez wrote: >> >> Hi. >> >> You can consider use pg_repack to remove bloat >> (http://reorg.github.io/pg_repack/) >> >> Best regards. >> >> Carlos Martinez >> >> >> On Mon, Nov 27, 2017 at 4:17 AM, Stefan Fercot >> wrote: >> > Hi, >> > >> > You can track that with some monitoring tools like check_pgactivity. The >> > queries can be found here : >> > https://github.com/ioguix/pgsql-bloat-estimation. >> > >> > Tables : >> > >> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql >> > Indexes : >> > >> > https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat.sql >> > >> > Backups should also be part of your maintenance plan. >> > Vacuum/Analyse/Reindex is already a good basis. >> > >> > >> > Kind regards >> > >> > >> > On 11/27/2017 10:10 AM, Vikas Sharma wrote: >> >> Hi All, >> >> >> >> Could someone please provide the query/queries to find table and >> >> Index bloating in PgSql 9.3 onwards? >> >> and are there any other maintenance tasks inPgSql except vacuum & >> >> analyse. >> >> >> >> Regards >> >> Vikas >> > >> > -- >> > Stefan FERCOT >> > http://dalibo.com - http://dalibo.org >> > >> > >> >
Re: Migrating Postgresql from Linux x86
Hi. We do that migration from Postgres 9.0 (x86_64, Centos 6) to Postgres 9.6 (power, SUSE Enterprise 12). The best way to us was to do a backup (pg_dump -Fc ...) and restore using several cores (pg_restore -j N ..., where N equals to the cores/jobs to be used). How many jobs launch to restore depends on: the number of cores available and the I/O throughput. Make some test to get the right number of jobs. Our restore task took around four hours. We had a downtime of seven hours. Other options are: * You can try a logical replication (maybe pg_logical). * Backup/restore without stop and then, make a downtime and synchronize the changed data between tables. We preferred the failsafe option and made a full downtime and we done the job from Saturday to Sunday with a holiday Monday (It is 24x7x365 business, and get a downtime is somewhat difficult). Best regards. Carlos M. On Fri, Feb 9, 2018 at 7:44 PM, Clive Anglin wrote: > > Hi > > Anyone have migrated PostgreSQL running on Linux x86_64 to running on Linux > on Power platform. Interested in a checklist of things to be done and if > there is a proper document with the steps to completing would also be of > interest. > > > > Thanks > > > > Clive A. > > > >
