Problems encountered when upgrading PostgreSQL
Previously, PostgreSQL was upgraded from 9.5 to 10.3 version, and the port was set to 5433 during installation (default is 5432). Later, I found that a syntax error occurred when using psql to restore db.
This error was caused by the PostgreSQL10.3 version adding the SQL syntax that was not available in version 9.5, but I have clearly upgraded the database, so why do I still encounter syntax errors?
And I found that after I connected to the database in the cmd window, psql prompted me that the version of psql is 10.3, while the version of server is 9.5.
After struggling for a while, I was crying by my stupidity.
It turned out that it was because I didn't pass when I connected to the database-p 5433To specify the server port number of version 10.5, so psql automatically connects to the 9.5 server with the default port number of 5432.
Solution
Specify the port number when connecting to the database
If you want to connect to a port number other than 5432, you must pass-pParameters to specify the port number.
Since I was originally using version 9.5, I started the server service with version 9.5, and the port number is the default 5432. Later, I upgraded the version 10.3. In fact, PostgreSQL will not uninstall the old version when upgrading, and will automatically start the server service after installing the new version. The port number I set here is 5433.
So I started the server service of 9.5 and 10.3 on my computer at the same time. When I did not specify the port number, it was actually a 9.5 version of server that was connected, so an error occurred when the restore used the new version of the syntax db.
Modify the default port number
If I want to connect to a new version of the server without specifying the port number, I can only change the port number of the server of 10.3 to the default port number 5432.
First, close the server service version 9.5, and this needs to be closed in the system's service manager:
- Win + R shortcut keys open and run;
- enter
Open the service manager; - Find the server service running version 9.5 and close it.
Under the installation path of PostgreSQL, find the corresponding version of the file (in the data folder):
- Revise
port = 5432； - Save the modifications and restart the server service of this version to take effect.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.