Who has never faced this? If this is your case, STOP READING NOW and run this command to prevent the vacuum from running:ALTER TABLE mytable SET ( autovacuum_enabled = false, toast.autovacuum_enabled = false ); Remembering that neither this post nor the library GUARANTEES recovery. There is a real possibility of recovery if a set of factors are met.continuing... You send messages in groups and the first replies: - Do you have a backup? - Revert the transaction - Stop the Postgres immediately Only that is not always possible. Examples: - On a high turnover basis, you cannot afford to stop it. - Database backup of more than terabytes is often expensive. - The transaction has already been executed, so how to revert it if other users have already made thousands of changes after yours?It made me look for alternatives and that's when I found pg_dirtyread
If your server is Linux, you can just follow the steps in the github readme. If your server is a windows server (which was my case), keep reading that we may be able to help each other.
Lets go Download the library and follow the steps below. Download the Visual Studio Community(or another version). Install VS with basic features and libraries. But don't forget to select C/C ++ compilers.To follow these instructions step by step you’ll also need a 64-bit PostgreSQL install. For 32-bit version, just a few changes to the folders are enough.Run the pg_config file to configure the environment. In my case, the file is in: C:\Program Files\PostgreSQL\9.3\bin\pg_config.exe It will open a screen and then close automatically, this behavior is normal.Creating your project
Create a new, blank C / C ++ project. Unzip the pg_dirtyread ZIP in the project folder and right click on "Source Files-> Add-> Existing Item ..." and selecting this files:
I hope that the pg_dirtyread project owner has validated my pull request with the changes when reading this file. If not, make minor changes to the source:1. After the last #include, insert the dirtread_tupconvert.h file. It would look like this: #include "dirtyread_tupconvert.h"2.Before the "Dataum" where the function code starts, insert the code: #ifdef _WIN32 / * Add a prototype marked PGDLLEXPORT * / PGDLLEXPORT Datum pg_dirtyread (PG_FUNCTION_ARGS); #endif #ifdef _WIN64 / * Add a prototype marked PGDLLEXPORT * / PGDLLEXPORT Datum pg_dirtyread (PG_FUNCTION_ARGS); #endif
PG_FUNCTION_INFO_V1 (pg_dirtyread); Datum pg_dirtyread (PG_FUNCTION_ARGS);Likes this:
And save everything.Visual Studio doesn’t know where the headers are, so it’ll highlight most of the file with error markers. We need to tell it where PostgreSQL’s headers and libraries are before we can build the project – and make a few other changes while we’re at it.Now let's configure the project to create the DLL
Right click on your project and go to properties
Select "All configurations" and the platform on which you want to generate your DLL. In my case 64-bit.
Select "All configurations" and the platform on which you want to generate your DLL. In my case 64-bit.General settingsUnder Configuration Properties -> General, set “Configuration Type” to “Dynamic Library (.dll)”.Under C/C++ -> Preprocessor Directives, add the directive “WIN32”. PostgreSQL doesn’t recognise “WINDLL” as equivalent so we need to explicitly tell it when we’re on a win32 platform. This will be fixed in a future version.Under C/C++ -> Code Generation, set “Enable C++ Exceptions” to “No”, and under “Advanced” set “Compile As” to “C Code”.Under Linker -> Manifest File, set “Generate Manifest” to “No”.Under Linker -> Input -> Additional Dependencies, add postgres.lib and intl.lib to the library list(for version >= 11, included libintl.lib instead of intl.lib). Do not uncheck “Inherit from parent or project defaults”. Trivial extensions don’t require this, but if you link to any server functions you’ll require it.Include and library directoriesNow you need to add the required include directories.In the properties dialog, go to Configuration Properties -> C/C++ -> General. In Additional Include Directories, pull down the arrow in the right of the textbox and choose “”. Now, by pasting paths or by folder selection, add the following folders inside your PostgreSQL install directory in this order(in my case C:\Program Files\PostgreSQL\9.3):include\server\port\win32_msvc include\server\port\win32 include\server includeOn my system that looks like:
You’ll also need to set the library path. Under “Linker”->”General”, in Additional Library Directories. In my case that’s C:\Program Files\PostgreSQL\9.3\lib. While you’re there, set Link Library Dependencies to No.Click “OK”. The error highlights on your extension file should go away when you return to the source file.Choose “Release” and "x64"(in my case), from the pull-down menu.
At this point you must fish the DLL out of the project and copy it into PostgreSQL’s lib directory. In my case that means copying C:\Users\suporte\source\repos\pg_dirtyread_win\x64\Release\pg_dirtyread.dll to C:\Program Files\PostgreSQL\9.3\libBut for the extension to work properly in Postgres, we need to configure some other files.
Remembering that I did a pullrequest for the project owner and I am waiting for the release release. If it hasn't already, make minor changes to the pg_dirtyread - **. Sql files, including the word "STRICT" after "LANGUAGE C" as shown bellow.
Save everything.Copy the files to the Postgres extension folder. In my case(C:\Program Files\PostgreSQL\9.3\share\extension)
If everything went well, you can now run the command: "create extension pg_dirtyread"
In my case:
My table contained bytea fields. Then it would be more difficult to just insert the information. So, I decided to export to binary files and then import the records.Execute in prompt MS-DOS: psql -U postgres -d my_database\copy (SELECT * FROM pg_dirtyread('my table') AS t(field1 character(39), field2 bytea, field4 character varying, field5 timestamp without time zone, field6 bigint) ) to 'F:\recovery.bin' with BINARY;After a while the export ended. Then I ran the command below to import into the table.copy my_table from 'F:\recovery.bin' BINARY;
Programmer for over 20 years. Venturing into the most diverse areas: IP PBX, Database (SGDB, noSQL), Delphi, Java, PHP, Python, NodeJS, VueJS..and counting
