Change your site from MySQL to PostgreSQL

"Guide" This article is divided into two parts, the first part of my motives for this conversion, and step-by-step explanation of how the existing MySQL data into the postgres. The second part will explain how to adjust PHP to the new database system.

This article is divided into two parts, the first part describes my motives for this transformation, and explains Step-by-step how to convert existing MySQL data into Postgres. The second part will explain how to adjust PHP to the new database system.

Motives of the conversion

The first time I learned Postgres was in an article in the Phpbuilder website. This article compares postgres with MySQL, when I was using MySQL. But when I read this article, I was fascinated by postgres-but I hadn't thought of redesigning my site yet.

I continue to use MySQL because my hosting provider can only provide MySQL support, which I cannot change. Until one day, the host provider's host crashed. I switched to a host provider immediately, and the new host provider was a lot different than the original one, and they made more commitment to me in terms of security and stability. The new company tried to convince me to use Postgres because the postgres was more stable than MySQL, but I didn't accept the proposal because my site has completed all the coding work based on MySQL. They had to install MySQL specifically for my site. So the question began.

My first job was to copy MySQL data from the old server to the new host. First, I dump the existing data into a SQL file, and then import the SQL file on the new host. MySQL crashed quickly when dealing with this thousands of-line file. After restarting MySQL, about half of that data was successfully imported, and MySQL only worked intermittently. Finally, they had to delete the information that had been imported and let me try again. MySQL crashes again. This was repeated several times until I finally decided to split my SQL file into pieces. I had to try it a few more times, and finally I managed to import most of the data into the new MySQL server. It was all right and I was relieved.
In the following months, MySQL crashes almost every two weeks, the most painful of which is at the end of June 2001. This time, the data stored in MySQL is completely destroyed. I have a backup file for SQL, but because of the painful experience of importing a lot of data into MySQL this time I don't want to recover the data through this backup again. At this point, the company again advised me to turn to my site, using Postgres. As a result of MySQL's failure, I finally accepted the proposal.

Transfer data from MySQL to Postgres

Moving data from MySQL to Postgres is a small challenge, as Postgres supports more standard formats for SQL than MySQL, and it is not possible to use SQL's dump results directly in Postgres. However, the SQL syntax is quite similar, so it doesn't take much time for me.

Conversion of MySQL Dump results

First, ask your host provider to set up a database for your account. Like the MySQL database, the Postgres database consists of a series of data tables that contain actual data. Then, use the mysqldump command to make a dump file for your MySQL database.
Mysqldump-u username-p databasename > Sqldump.txt

Use FTP to download the entire dump file. Now that you have this SQL file on your computer, you can convert it into a file that Postgres can import.

First, cut all MySQL CREATE table queries from the dump file and paste them into a separate text file. The next step is to redefine the datasheet using a language that Postgres can understand.

Postgres SQL for tables is very similar to MySQL, but not exactly the same. Here is an example:
CREATE TABLE practicetable
{
Someid Serial,
Time TIMESTAMP DEFAULT now (),
Name VARCHAR (50),
Address VARCHAR (50),
City VARCHAR (50),
State VARCHAR (2),
Country VARCHAR (3) DEFAULT ' USA ',
Postlcode VARCHAR (15),
Age smallint,
Lattitude Real,
Longitude Real,
Somebool Boolean,
Message Textitem
};