Postgresql Upgrade using dumpall...botched

daBee · Apr 24, 2018

Hi folks.

Well, updated Postgresql from 9.6 to 10.3.1 after a pg_dumpall. Now I can't get the new version started. Not sure where to take it from here, as I've tried things and it won't initdb:

Bash:

[Tue Apr 24 11:20:21 adminuser@serverbox /var/db/postgres] sudo service postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

 fixing permissions on existing directory /var/db/postgres/data10 ... initdb: could not change permissions of directory "/var/db/postgres/data10": Operation not permitted
[Tue Apr 24 11:20:26 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 11:20 .
drwxr-xr-x  16 root      wheel      26 Apr 24 11:14 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwxr-xr-x   2 root      postgres    2 Apr 24 11:20 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 11:20:58 adminuser@serverbox /var/db/postgres] sudo service postgresql start
pg_ctl: directory "/var/db/postgres/data10" is not a database cluster directory

Now, I CAN change the permissions on this, but it won't work anyway. The user I am using as an admin is not postgres, obviously.

At this point, I'd like to obscure the username and password, and have access to the server admin run as adminuser. Is this possible? I can only think that this setup can be designed. I can't get a role to start anyway. The beginning of Postgresql has always been confusing.

Any insight appreciated. Cheers

SirDice · Apr 24, 2018

chown postgres /var/db/postgres/data10

daBee · Apr 24, 2018

SirDice said:
chown postgres /var/db/postgres/data10

Yes I've tried that, and it didn't work. I said that above.

Bash:

[Tue Apr 24 09:07:21 adminuser@serverbox /var/db/postgres] sudo chown postgres data10
[Tue Apr 24 09:07:42 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 08:31 .
drwxr-xr-x  16 root      wheel      26 Apr 24 07:45 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwxr-xr-x   2 postgres  postgres    2 Apr 24 08:31 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 09:07:43 adminuser@serverbox /var/db/postgres] sudo chmod 700 data10
[Tue Apr 24 09:07:59 adminuser@serverbox /var/db/postgres] ll
total 23
drwxr-xr-x   4 postgres  postgres    5 Apr 24 08:31 .
drwxr-xr-x  16 root      wheel      26 Apr 24 07:45 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwx------   2 postgres  postgres    2 Apr 24 08:31 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 09:08:00 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/db/postgres/data10 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start

[Tue Apr 24 09:08:09 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql start
2018-04-24 09:10:11.989 EDT [29981] LOG:  listening on IPv6 address "::1", port 5432
2018-04-24 09:10:11.989 EDT [29981] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-24 09:10:11.991 EDT [29981] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-24 09:10:11.996 EDT [29981] LOG:  ending log output to stderr
2018-04-24 09:10:11.996 EDT [29981] HINT:  Future log output will go to log destination "syslog".
[Tue Apr 24 09:10:12 adminuser@serverbox /var/db/postgres] c
[Tue Apr 24 09:10:36 adminuser@serverbox /var/db/postgres] pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied
[Tue Apr 24 09:13:26 adminuser@serverbox /var/db/postgres] sudo pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

So it's permissions issues on a 700.

BSDAppentic3 · Apr 24, 2018

daBee What you want to exactly? Give permissions to another user?

daBee · Apr 24, 2018

I just want to get it going. I'm upgrading from 9.6. I did a pg_dumpall, and I can't even get this one to start up, even before any roles have been assigned. The server isn't running, because the data folder has permissions issues.

SirDice · Apr 24, 2018

As it's still new and there's nothing in the database yet, rm -rf /var/db/postgres/data10 then run service postgres initdb. Once that's done service postgres start.

BSDAppentic3 · Apr 24, 2018

daBee So you upgraded from 95 to 96?
Edit: You had give permissions to you actual account? Your user, it's in wheel, or it has full access but not like the root?
You're using this?: postgresql10-server-10.3_1 PostgreSQL is the most advanced open-source database available anywhere

daBee · Apr 24, 2018

From 9.6 to 10, hence the need for an upgrade.

uid=1001(adminuser) gid=1001(adminuser) groups=1001(adminuser),0(wheel)

When I use that user as owner, I go to 700, which is the same ownership of the /data96 previous data directory.
Edit: Know that I did a pg_dumpall and not the pg_upgrade

ShelLuser · Apr 24, 2018

Why are you using pg_ctl to start your setup after you used the service command? That made little sense to me and probably also explains the error message for /var/db/postgres/data10 (at least to some degree).

This is also an unusual place for the pid file (default is in the actual data directory) so did you perhaps change any specific defaults, and if so how did you do that? Specifically: did you use specific parameters in /etc/rc.conf or did you rely on postgresql.conf?

Anyway, first I'd follow up on SirDice 's suggestion while also keeping your custom settings in mind.

Also don't rely on pg_ctl to start/stop the server, use # service postgresql start/stop instead.

daBee · Apr 24, 2018

Probably because the service didn't show up as having started up. I've not seen it started up on port 5432. I changed no defaults. I simply installed it and tried to get some users going, and I ran into the permissions issues. At this time I can't find postgresql.conf anywhere outside the /data96 directory.

Can I start again? I can delete both directories as I have a dump already.

ShelLuser · Apr 24, 2018

daBee said:
At this time I can't find postgresql.conf anywhere outside the /data96 directory.

Can I start again? I can delete both directories as I have a dump already.

Yah, follow SirDice's earlier suggestion. Delete the data10 directory, use the initdb parameter and then try to start it. That should do it.

BSDAppentic3 · Apr 24, 2018

All right...
I've installed postgresql96-client-9.6.8. Then I installed postgresql10-server-10.3_1.
See the output:

Code:

$ sudo /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied

This means that I have no problems installing and upgrading it. The trouble was when I try to launch it. It didn't leave me launch it as non-root, neither as root, so hth I must launch it?
I think that I must add my actual account to launch the program.
I'm right?

BSDAppentic3 · Apr 24, 2018

ShelLuser said:
Why are you using pg_ctl to start your setup after you used the service command? That made little sense to me and probably also explains the error message for /var/db/postgres/data10 (at least to some degree).

This is also an unusual place for the pid file (default is in the actual data directory) so did you perhaps change any specific defaults, and if so how did you do that? Specifically: did you use specific parameters in /etc/rc.conf or did you rely on postgresql.conf?

Anyway, first I'd follow up on SirDice 's suggestion while also keeping your custom settings in mind.

Also don't rely on pg_ctl to start/stop the server, use # service postgresql start/stop instead.

You MUST see this:

Code:

$ sudo service postgresql onestart
2018-04-24 17:52:15.166 -03 [16377] LOG:  listening on IPv6 address "::1", port 5432
2018-04-24 17:52:15.167 -03 [16377] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-24 17:52:15.199 -03 [16377] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-24 17:52:15.240 -03 [16377] LOG:  ending log output to stderr
2018-04-24 17:52:15.240 -03 [16377] HINT:  Future log output will go to log destination "syslog".

What does it means? I had been success running it?

ShelLuser · Apr 24, 2018

BSDAppentic3 said:
I think that I must add my actual account to launch the program.
I'm right?

Please don't hijack someone's thread like this. All you're doing here is diverting the attention away from the actual problem as shared by daBee, this thread is about his problem first and foremost.

Your "problem" is non-existent, as you just displayed yourself, and it's not helping the OP.

BSDAppentic3 · Apr 24, 2018

ShelLuser said:
Please don't hijack someone's thread like this. All you're doing here is diverting the attention away from the actual problem as shared by daBee, this thread is about his problem first and foremost.

Your "problem" is non-existent, as you just displayed yourself, and it's not helping the OP.

If you see my last post, you'll see that I'm trying to launch the same program

BSDAppentic3 · Apr 24, 2018

ShelLuser Anyway, I think that he must solved it now. But I'm reproducing it just for the case that someone that have the same/similar problem, have a clue to solve it.
I don't think that I'm ruining not "hijacking" something right now. Sorry if I did it before, but now I think I make you a question.

BSDAppentic3 · Apr 24, 2018

ShelLuser In fact, since I installed the program and tried to launch the program with no success, I think that I have a problem. Not the same, because it is only a reproduction of such.

daBee · Apr 24, 2018

ShelLuser said:
Yah, follow SirDice's earlier suggestion. Delete the data10 directory, use the initdb parameter and then try to start it. That should do it.

Still the same problem:

Bash:

[Tue Apr 24 04:11:58 adminuser@serverbox /var/db/postgres] sudo rm -R data10/
[Tue Apr 24 04:12:06 adminuser@serverbox /var/db/postgres] sudo /usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data10 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start

[Tue Apr 24 04:13:08 adminuser@serverbox /var/db/postgres] ll
total 31
drwxr-xr-x   4 postgres  postgres    5 Apr 24 16:13 .
drwxr-xr-x  16 root      wheel      26 Apr 24 14:14 ..
-rw-------   1 postgres  postgres  731 Aug  9  2017 .psql_history
drwx------  19 postgres  postgres   24 Apr 24 16:13 data10
drwx------  20 postgres  postgres   26 Apr 24 08:29 data96
[Tue Apr 24 04:13:11 adminuser@serverbox /var/db/postgres] /usr/local/bin/pg_ctl -D /var/db/postgres/data10 -l logfile start
pg_ctl: could not open PID file "/var/db/postgres/data10/postmaster.pid": Permission denied
[Tue Apr 24 04:13:39 adminuser@serverbox /var/db/postgres]

Here are permissions on that directory:

 drwx------  19 postgres  postgres   24 Apr 24 16:13 data10

drwx------  20 postgres  postgres   26 Apr 24 08:29 data96

So it's a 700, same user as my data96 directory. Not my day at all.

Ah hold on a minute...still messin around here...

daBee · Apr 24, 2018

OK, it says it's not in /etc/rc.conf, or is not executable. So I'm going to do a fresh restart. I might be back. Heh.

ShelLuser · Apr 24, 2018

daBee said:
Still the same problem:

Ok, the problem here is that your last step is wrong. Sort off.

I know that the system tells you to use pg_ctl to start the service but you should not do this. Instead always use service. So: service postgresql start.

Here's what I would do at this point:

cd /var/db/postgresql
sudo sh
rm -rf data10
service postgresql initdb
service postgresql start
exit

What's causing your error is that at the final step you're logged on as adminuser. But the directory /var/db/postgresql/data10 is owned by postgresql and its permissionmask is 700. By default the PID file for the process gets placed in the data directory, but your current account has no permission there. So it cannot create the pid file nor can it access the actual datafiles.

But the above list of commands will start the server.

daBee · Apr 24, 2018

Bash:

cd /usr/ports/databases/postgresql10-client
sudo make install clean
cd /usr/ports/databases/postgresql10-server
sudo make install clean

<reboot>

~] service postgres initdb
postgres does not exist in /etc/rc.d or the local startup
directories (/usr/local/etc/rc.d), or is not executable

It is indeed there: postgresql_enable="YES"

No data directories at all. That was a complete fresh install after a complete uninstall.

Any ideas?

daBee · Apr 24, 2018

ShelLuser said:
Ok, the problem here is that your last step is wrong. Sort off.

Here's what I would do at this point:

cd /var/db/postgresql

sudo sh

rm -rf data10

service postgresql initdb

service postgresql start

exit

What's causing your error is that at the final step you're logged on as adminuser. But the directory /var/db/postgresql/data10 is owned by postgresql and its permissionmask is 700. By default the PID file for the process gets placed in the data directory, but your current account has no permission there. So it cannot create the pid file nor can it access the actual datafiles.

But the above list of commands will start the server.

OK that got it working.

postgresql.conf is in the data folder, protected. Where should I move it?

ShelLuser · Apr 24, 2018

daBee said:
OK that got it working.

postgresql.conf is in the data folder, protected. Where should I move it?

Leave it, that's where PostgreSQL expects the file to be as far as I know. At best you could make a symbolic link in /usr/local/etc for easier access, but that's as far as I'd take this.

Now that you got a working setup it's time to put psql to work and restore your data. Or... you could consider copying data96 and then performing an upgrade. But restoring is probably easier.

daBee · Apr 25, 2018

All done. Worked well.

BSDAppentic3 · Apr 25, 2018

ShelLuser
Hey, sorry if I get a bit angry or molest.
If you want to take a look of what I get when I ran

Code:

 service postgresql start

, well, here's mine:

Code:

$ sudo service postgresql onestart
2018-04-25 15:07:33.288 -03 [17352] LOG:  listening on IPv6 address "::1", port 5432
2018-04-25 15:07:33.288 -03 [17352] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-04-25 15:07:33.289 -03 [17352] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-04-25 15:07:33.333 -03 [17352] LOG:  ending log output to stderr
2018-04-25 15:07:33.333 -03 [17352] HINT:  Future log output will go to log destination "syslog".

The output means that I had been successful launching the command that you provided?
Thanks.

Postgresql Upgrade using dumpall...botched

daBee

SirDice

Administrator

daBee

BSDAppentic3

Guest

daBee

SirDice

Administrator

BSDAppentic3

Guest

daBee

ShelLuser

daBee

ShelLuser

BSDAppentic3

Guest

BSDAppentic3

Guest

ShelLuser

BSDAppentic3

Guest

BSDAppentic3

Guest

BSDAppentic3

Guest

daBee

daBee

ShelLuser

daBee

daBee

ShelLuser

daBee

BSDAppentic3

Guest